?
Solved

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

Posted on 2006-07-12
9
Medium Priority
?
909 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
8 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

807 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