Conversion failed because the data value overflowed

[dkillilea]

I am having a problem with inserting data into a table from an OLE DB connection. 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 "[LinkedServer]..[Schema].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "LinkedServer". 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 generic 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 "[LinkedServer]..[Schema].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "LinkedServer". 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.

 

[dkillilea]

LNBruno - I'm trying to use openrowset as well as run off of a new linked server.

gmmastros - I know we recently installed SP2 so here is the result of that query:
9.00.3050.00 SP2

 

[dkillilea]

I dropped my linked server and that didn't work.

I followed LNBruno's link to the MDX fix for MSOLAP and selected "Allow inprocess" to no avail with the following types of queries: openrowset and openquery.

I searched MSDN, MS Help and Support and all over Google with every possible search option. NO ONE has a solution for this. Is there a way to notify Microsoft of the problem so they can publish a fix for us?