SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk insert data conversion error (truncation)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abuhassan
Posting Yak Master

105 Posts

Posted - 12/04/2006 :  12:26:38  Show Profile  Reply with Quote
hi

"Bulk insert data conversion error (truncation) for row 1, column 1 (id)."

when you get the error above or similar in sql server 2000 does it continue inserting the data by truncating it or does it stop beacause looking at the data that i have got it seems to continue inserting the data but just truncates the colunm. i have tried it several time its seeems to be consistent.

I have data that has white spaces after the actual data e.g. '00093 ' hence i am happy aslong as i can be sure that it does always continue as i will be loading alot of data using a similar process.

hence my question is that will it load all the data all the time and just truncate it to fit the column size?

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/04/2006 :  19:48:05  Show Profile  Visit nr's Homepage  Reply with Quote
It's an error so should stop when it reaches your error tolerance - think the default is 5.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 12/06/2006 :  09:43:11  Show Profile  Reply with Quote
How can i increase or switch off the default error tolerance?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 12/06/2006 :  09:52:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Increase you destination table column widths to accomodate the imported data.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/06/2006 :  09:55:55  Show Profile  Visit nr's Homepage  Reply with Quote
There's a maxerrors value you can include.
Have a look in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 12/06/2006 :  10:45:05  Show Profile  Reply with Quote
Thanks for that

Ive added the maxerrors to 50000 that worked

i just wanted to ask if i didi the following i.e. "Increase you destination table column widths to accomodate the imported data." and then reduced to the original will that automatically truncate the colunms?

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 12/06/2006 :  10:50:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
After importing, adjust your data with

update yourtablenamehere
set yourcolumnnamehere = rtrim(yourcolumnnamehere)

and then insert into target tables.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 12/07/2006 :  11:50:36  Show Profile  Reply with Quote
Many thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000