Solved

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

Posted on 2006-07-12
9
883 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
  • 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now