Solved

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

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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 …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 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

19 Experts available now in Live!

Get 1:1 Help Now