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

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?

blackdsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NetminderConnect With a Mentor Commented:
Closed, 125 points refunded.
Netminder
Site Admin
0
 
LowfatspreadCommented:
is the column defined as decimal(17,9) (at least)?
0
 
blackdsAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LowfatspreadCommented:
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
 
blackdsAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
blackdsAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.