Conversion failed because the data value overflowed the data type used by the provider.

  • Alex Kirwan

    Old Hand

    Points: 332

    Hi all,

    This is a follow up to a previous post from aprox 2 years ago.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=145803

    I have stored procedures set up to import tables from a PROGRESS DB to SQL 2005 via linked server.

    The import process works fine Id say 50% of the time. Usually works fine after business hours when all users are logged out of system. However I do get this error message and it fails about half the time I run the script.  I noticed this problem was raised in the link above so any help adive would be greatly apreciated!

    I presumed it was some kind of dat type conversion issue??.... PROGRESS stores date as a datatype DATE - dd/mm/year I have the comolmn in my SQL set up as DATETIME.  Could this be a problem??  I dont think this is the problem, as I said the script does work 50% of the time.  Also if I import the date column alone then it seems to work no problem.  

    My thinking is that it is to do with the data on the PROGRESS side is forever updating (as thats our LIVE DB) so when the data is copying over from PROGRESS to SQL it can fail due to an increase in data from PROGRESS?? - could that be possible reason to fail??

    Here is the message in full:

    OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Msg 7341, Level 16, State 2, Procedure ImportButlersVAT_LIVE, Line 4

    Cannot get the current row value of column "[MSDASQL].fdate" from OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL". Conversion failed because the data value overflowed the data type used by the provider.

    As always - Thanks in advance!

  • This was removed by the editor as SPAM

  • Scott Bauer

    SSC Journeyman

    Points: 77

    I am having the exact same problem.  Something funky is going on behind the scenes with the OLE DB engine and what it sees in the field.  I know the data type for my column s/b numeric(12,2)  yet the error keeps coming back:

    Cannot get the current row value of column "[MAJESTI]..[root].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "MAJESTI". Conversion failed because the data value overflowed the data type used by the provider.

    I tried casting as varchar(max) and that wouldn't work. 

    I know you can't trim numbers(or maybe you can) but I'm pretty sure that wouldn't work b/c it's a data overflow issue.

    I get the same error even if I do a insert with case when <=9999 else '0' 

    *************************************************************

    What I see with a link to this table through Access instead of SQl Server:

    I can see the number with Access 1997:

    3.53E+11

    , or 353,223,500,185.92

    And I only see this number with Access 97. In Access 2003 I can NOT see the number because it throws the following error message and puts #Error in the field. 

    "The decimal fields precision is to small to accept the numeric you attempted to add."

    *************************************************************

    Now that I actually know what the number is that's causing the error message in Sql Server I tried casting as numeric(36,2) and I still get the error message: 

    Cannot get the current row value of column "[MAJESTI]..[root].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "MAJESTI". Conversion failed because the data value overflowed the data type used by the provider.

    Anyone have a clue what I could do to get around/fix this problem with eliminating the column? Any help would be greatly appreciated.

     

     

     

     

  • smv929

    SSCrazy

    Points: 2889

    This won't help much, but...from what I understand, Progress allows any size value to be inserted into a field even though the schema may have char length defined. Don't know why, but that's how it is. However, odbc drivers and the rest of the database world seems to care about char lengths and precision. They fail when they encounter such a value.

    In some cases , you can get it to work:

    1. for strings, you can use left(field,x) to limit the number of fields returned. However, in some cases, the overstuffed field maybe too big and even that doesn't work. I've had some success importing data into a staging table having field widths of 200. Again, even that sometimes does not work.

    2. For numbers or dates that throw an "exceeds precision" error, you might try using a progress query...

    select to_char(price) "price" from table

    Then in the sql server query that handles that result set, use...

    select price = cast(price as money) from...

    Apparently, you're suppossed to be able to define in the Progress data dictionary data type limits for one or more fields, so that it resolves this situation. But I have not gotten a clear answer on it.


    smv929

  • ccrosbie

    SSC Enthusiast

    Points: 189

    I'm having the same problems with a Pervasive database when linked to SQL Server. It seems to only be a problem with date fields.

  • bcronce

    SSCrazy

    Points: 2537

    the DB doesn't actually 'store' the data like dd/mm/year. Usually it's a 32/64bit integer based from some starting date counting x units of time forward. It's possible that when you transfer a date to a datatime, it may be converting incorrectly. Are you dates correct for data that has transfered?

  • ccrosbie

    SSC Enthusiast

    Points: 189

    I can't see any dates that get transfered. I only get an error message on my query. If I link the tables into Access, the dates come across correctly.

  • Mladen Krstonosic

    Valued Member

    Points: 65

    Maybe will help to someone:

    here is what i did,

    select * from openquery

    (LINK_LINUX, 'SELECT id, inf_date_open, inf_date_clos, inf_prob_type_no,

    CAST(inf_prob_desc AS varchar(4000)) AS inf_prob_desc,

    CAST(inf_vp_prob_desc AS varchar(4000)) AS inf_vp_prob_desc ,

    inf_last_upda, inf_last_upda_by, inf_nickname,

    inf_inpu_type_no, inf_prob_cate_no FROM public.Info')

    MK.

  • vermarajiv

    SSC Enthusiast

    Points: 152

    I was having the same problem. I would get this errors for date fields. I used Linked server and converted all the dates to character by using TO_CHAR. Apparantly it worked. I still have problems making it work through DTS though. Its interesting that it doesnt like the CHAR fields.

  • Jayaram Krishnaswamy

    SSC Eights!

    Points: 970

    I have come across the same type of problems linking to EnterpriseDB. My first impression is that the combination of MSDASQL and the ODBC driver I am using are not a matched pair. I have [experimented]eliminated columns that do not provide the result of a query and recreated the linking to see how far I can go. The above observation is based on this experiment.

  • ravi.shesham

    SSC Journeyman

    Points: 92

    Hi All,

    I am also gettitg error like below any help me out

    Server: Msg 7346, Level 16, State 2, Line 1

    Could not get the data of the row from the OLE DB provider 'SQLOLEDB'. 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 'SQLOLEDB' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=bigintColumn STATUS=DBSTATUS_E_DATAOVERFLOW]].

    am trying to use below select stmt

    select bigintColumn

    from OPENDATASOURCE('SQLOLEDB','Data Source=<>;User ID=uis;Password=pwd').mydb.dbo.myview as

    Thanks

  • svessels

    SSC Rookie

    Points: 37

    I have been successfully able to get around it by using to_char(numeric_field) as in

    select

    item,

    cast(units as int)

    from openquery(linkedserver,

    'select item,to_char(sum(units)) units

    from pub.tbl

    where x=y

    group by item')

    The same goes with openrowdataset(). You'd have to spell out the fields instead of using select *.

  • harigopal_sa

    SSC Journeyman

    Points: 96

    hi friends,

    I faced the same issue in a slightly different scenario. We were using a link server to connect to an SQL Analysis Services database and when we actually ran an MDX query against the lnked server we got the error "Conversion failed because the data value overflowed the data type used by the provider". On closer inspection of the query we found that the MDX query had a calculated member in the query and the denominator part was becoming zero in certain scenarios, in which case it will return a number with too many decimal points. we modified the query to eliminate division by zero and this error got solved. So I guess the large decimal precision returned by the SSAS engine was too much for the MSOLAP provider to handle in my case.

    Hope this helps .

    Regards

    Hari

  • Suth

    SSCommitted

    Points: 1559

    Try rounding the column in the where clause and select it as a datetime field. It worked for me. problem is there maybe a date which is further than year 9999 and you will be able to find the rows which are causing you problems. Copy and paste the number into excel and change the format in excel to date and itll show you the date that its trying to state and you may face a ########## which is the problem.

    SELECT

    CASE WHEN [Column]< 2958465

    THEN cast([Column] as datetime) ELSE null END AS [Column]

    FROM

    OPENQUERY ([LinkedServer],'select round([Column],4) [Column]from [Table] ')

  • Michael_O

    Ten Centuries

    Points: 1244

    Mladen Krstonosic (10/27/2008)


    Maybe will help to someone:

    here is what i did,

    select * from openquery

    (LINK_LINUX, 'SELECT id, inf_date_open, inf_date_clos, inf_prob_type_no,

    CAST(inf_prob_desc AS varchar(4000)) AS inf_prob_desc,

    CAST(inf_vp_prob_desc AS varchar(4000)) AS inf_vp_prob_desc ,

    inf_last_upda, inf_last_upda_by, inf_nickname,

    inf_inpu_type_no, inf_prob_cate_no FROM public.Info')

    MK.

    Marvelous Mladen!! After 2 years you posted your comment it just solved my problem today!! Thank you!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply