BCP error

[ezecastle]

Hi guys, I'm having issues with a xp_cmdshell bcp. I try to load about 200 rows of data and get about 150 in but see this error

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0

I take the 50 rows that error out and create a new text file to bcp in and the records get loaded using the same command that was used for the original file. Any ideas why these 50 records aren't loaded in the original file? but just copy them to another text file and they load. thanks.

[skhanal]

Is there any special character at the end of 150th record?

[nosepicker]

Most likely, if you are getting that error, it's because one of the strings is too long for the column into which it's being inserted. And sometimes a record will still get BCP'd into the table (albeit partially) even when this error is produced. I would say create a "dummy" table with each column expanded to a large width. Then BCP the data into this dummy table and then you can do some comparative analysis between it and your original table to find out where the offending record and column is.

[ezecastle]

Thanks, i created a new table and doubled the sizes and it worked.

[LuckDuck]

Encountering the same problem importing data from xls into sql server 2000, except that increasing the field size does not solve it. The rows of data that are inserted in the table have weird encoding, and the error file (C:\error.txt) produces:

#@ Row 1, Column 2: String data, right truncation @#
Ã�Ã�Ã*¡±á  
#@ Row 2, Column 1: String data, right truncation @#
... etc.

Is there a limit to the number of rows that can be inserted at a time? The error in the cmd console shows:

Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0



help ? please ...

regards,
luckduck ... no luck

[nosepicker]

If I had to guess, I'd say there are either line feed, carriage return, or tab characters buried within your "weird encoding" (or at least they're being interpreted that way by SQL Server). If that's true, it could cause BCP to insert the wrong data into the wrong column, since all BCP looks for is row delimiters and column delimiters. Try creating a table with a single column with datatype definition of something like varchar(8000) (or whatever would be wide enough to accommodate an entire row). BCP the text file into this single-column table, which should basically insert an entire line of text as a row into the column. Then you should be able to look at the data or query it to find out if there are any hidden line feeds, carriage returns, or tabs.

[LuckDuck]

You were right about the weird encoding. The xls did not have proper row/column delimiters. The BCP worked after re-saving the file as type Tab Delimited. Thanks for the tip. Lots appreciated!