Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conversion failed because the data value overflowed

Status
Not open for further replies.
dkillilea

dkillilea

MIS
Dec 7, 2004
41
US
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.


 
Sort by date Sort by votes
If the data is correct and is too big for your current structure, the only choice is to change the current structure to accomodate that. If the data is wrong and you need to adjust it. Consider loading first to a work table that will allow the larger size field and then scrub out the bad data, then load to your produciton table.

Questions about posting. See faq183-874
 
Upvote 0 Downvote
I can't get it to go in any format type or size, whether it be varchar(max), numeric(38,2) etc. I don't think the problem is getting it to fit because I've tried every possible field size and type.
 
Upvote 0 Downvote
Take a look at this page mugs132, it has your problem and short term and long term solutions.

[monkey][snake] <.
 
Upvote 0 Downvote
That is a great soution for the Access side of viewing this data. But I need to get it in SQL Server from the same OLE DB source that Access would be reading from.

Any idea what needs done on the SQL Server side of things?
I only showed the Access side to further explain the weirdness of this situation. But it is good to see the solution you posted. Thank you.

 
Upvote 0 Downvote
Well, since numeric isn't a valid data type in Access (I'm pretty sure), you may want to try to pull the data in as decimal(20, 2), instead of numeric(x, x).

[monkey][snake] <.
 
Upvote 0 Downvote
I'm not sure what you mean. I'm not worried about the problem with viewing this column in Access. I just want to fix the problem in SQL Server. Both Access and SQL Server are pulling the data from the same source(our company software program which is basically a flat file). Any ideas on how to fix this column so I can insert it into SQL Server?
 
Upvote 0 Downvote
Duh, my bad, ok what I'm saying is this though, try to pull the data into a column with data type decimal instead of data type numeric, see if there is some type of funky thing going on that won't let you pull the data in as numeric.

[monkey][snake] <.
 
Upvote 0 Downvote
look man numeric is the same as decimal. the problem is somewhere else, take a look at this
I have no problem inserting these velaue

Code: 
create table Frustrated (Col1 decimal(36,2),Col2 numeric(36,2),col3 float)


declare @f float,@f2 float

select @f = 3.53E+11 
select @f2 = 353223500185.92

insert Frustrated
select @f,@f,@f

insert Frustrated
select @f2,@f2,@f2


select * from Frustrated


and here is the output
Code: 
353000000000.00	353000000000.00	353000000000.0
353223500185.92	353223500185.92	353223500185.91998

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions
 
Upvote 0 Downvote
no comma's in the file, just listed them for illustration purposes.

I know decimal and numeric are the same.

So you all know this value (353223500185.92) is not a correct value. It will change once a sales order has been updated. It's actually a sales discount column that s/b numeric(12,2). We don't normally give sales order discounts over 100 billion dollars!!!! Nevertheless, I still have to import the table to SQL Server. I can't do that right now unless I skip that column.

Again, I think it has something to do with the OLE DB engine b/c we've exhausted every formatting type and nothing works. I'm listing the error code again.

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.
 
Upvote 0 Downvote
No. No MDX and no Anaylsis server. This is a simple import to SQL Server from our Company software. The two servers are connected directly to each other, nothing in-between.

 
Upvote 0 Downvote
LNBruno may be on the right track. I checked our linked server provider MSDASQL and our IT dept had it set up with the "Allow inprocess." Anyone understand what that does?

SqlDenis has a good point so I'll try and use SSIS right now.

 
Upvote 0 Downvote
I'm not able to create and insert into this table with SSIS b/c we do not have a USQL license for SSIS yet.

I have reviewed the MSDASQL provider options like LBRuno provided in his post. So far that looks promising or at least I'll learn a little about the different types of connections.

I fogot to post this earlier, not sure if this would help...
Msg 7341, Level 16, State 2, Line 3
 
Upvote 0 Downvote
Definitely sounds like a provider issue. Have you tried dropping and recreating the linked server?

< M!ke >
Acupuncture Development: a jab well done.
 
Upvote 0 Downvote
Please run this and post the results. I suspect the problem you are having may have been corrected with a service pack.

[tt][blue]
Select ServerProperty('ProductVersion'), Serverproperty('ProductLevel')[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
252
bharons
bharons
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
377
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
609
Chris Miller
Chris Miller
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
302
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
Part Id 3900 take wrong technology id as 7 and it must Be 2 because Feature Name and Value Exist?
Replies
4
Views
117
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top