Invalid data type for numeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Invalid data type for numeric

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2010-11-21 : 19:47:49

When trying to import a table from a sql 2000 db to a sql 2008 server. I get this error msg below. Turns out there is an out of range issue with the numeric field. How can I check for it before importing it or how can i get around this error? I had to bump up all my numeric fields in the destination table, but is there a better way to deal with these?

Copying to [dbo].[OI_VEND_PACK_SORTED_NODUPS] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid data for type "numeric".".
(SQL Server Import and Export Wizard)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-21 : 20:47:54

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153205

Double post.

Poor planning on your part does not constitute an emergency on my part.

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2010-11-22 : 07:47:48

When I did a max(len()) on the numeric fields...I found that there was 1 that had a precision of 11 and the max(len()) for that field was 13, it was out of range. This was the problem, Is there a function or script to check for this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 08:23:39

I think you already have it
select max(len(col))
from tbl

will show whether you have any issues

select *
from tbl
where len(col) > 11

will show those greater than 11 characters.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources