Solved

Problem with MSDASQL passing large numerics to SQL Server from Progress database

Posted on 2006-07-12
9
900 Views
Last Modified: 2008-01-09
I've built DTS packages to synchronize tables from a Progress database to SQL Server.  One of the packages bombs with the error:
----------
"Insert error, column 9 (tr_begin_qoh, DBTYPE_NUMERIC), status 12: Invalid status for bound data.  Unspecified error."
----------
I've narrowed down the offending record and one of the fields has a value of "-14513785.863839400".  I know this because MSQuery has no problem retrieving this data via the ODBC connection to the Progress database.

Using SQL Query Analyzer to look at the data (via a linked server) I get this:
---------------
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[MSDASQL].tr_begin_qoh' from the OLE DB provider 'MSDASQL'. Conversion failed because the data value overflowed the data type used by the provider.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e21:  Data status returned from the provider: [COLUMN_NAME=tr_begin_qoh STATUS=DBSTATUS_E_DATAOVERFLOW]].
--------------
Any way to get MSDASQL to pass these values?

0
Comment
Question by:blackds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17091964
is the column defined as decimal(17,9) (at least)?
0
 

Author Comment

by:blackds
ID: 17092233
The column in the SQL Server db is defined as decimal of length 9 (precision = 17, scale = 10).  
But isn't this moot?  I can't even retrieve the piece of data from within SQL Query Analyzer.  This is the statement:
select * from openquery(csmfprd, 'select tr_begin_qoh from tr_hist where tr_trnbr=521059')
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17093150
have you tried "converting" the tr_begin_qoh  to a string?

e.g.

select * from openquery(csmfprd, 'select Convert(varchar(19),tr_begin_qoh)  as tr_begin_qoh
   from tr_hist where tr_trnbr=521059')

or
select convert(varchar(19),tr_begin_qoh) as tr_begin_qoh from openquery(csmfprd, 'select tr_begin_qoh
   from tr_hist where tr_trnbr=521059')

but i'd expect the first... to work best if you can find out the PROGRESS Syntax to achieve the conversion....

hth


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:blackds
ID: 17094061
Yes, I've been hammering on the "conversion" as a workaraound for a couple days now.  Your first example above is closer to the correct syntax as the second example just barfs up the same error message as before.  As soon as I stumble onto the correct syntax, the workaround may be my only recourse.  When/If I do, I'll post it here.
In the mean-time, any more ideas about how to get MSDASQL to stop being so snitty and "play well with others"?...
Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17094648
If it is any consolation you are not the only one having problems with Progress:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=145803
0
 

Author Comment

by:blackds
ID: 17103195
One of my corporate Progress gurus gave me the syntax - use the "string" function.
 From within SQL Query Analyzer I issued this query:
select * from openquery(csmfprd,'select string(tr_begin_qoh) from tr_hist where tr_trnbr=521059')
and got "-14798396.4108058".


Thanks to all for the help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17105055
>>Thanks to all for the help.<<

Great! Now please close thee question.  Here's how:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17156931
Closed, 125 points refunded.
Netminder
Site Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question