July 18, 2010 at 11:22 am
I have a stored procedure that uses the BCP utility to import data from a flat file using a format file.
Every column in the destination table is defined as VARCHAR() and every field in the format file is SQLCHAR.
My problem is if I use SQL management studio and try to change any data in any column (using file open) I am not able to change anything. I get the error "String or Binary data would be truncated". This occurs even if I mere blank out a column.
Does anyone have any ideas ?
July 18, 2010 at 12:35 pm
Do the changes you're making make some data wider than the column that it was intended for?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2010 at 6:10 am
No, I am not trying to make the data any longer. In some cases I just want to change the content or blank it out.
July 20, 2010 at 6:35 am
You probably need to look at the file itself. One of the rows is probably causing the error. Have you tried to run bcp from command line (witout the sp) and output the errors? Try the first couple of rows in the file and see what that does.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2010 at 9:09 pm
bryan.duchesne (7/20/2010)
No, I am not trying to make the data any longer. In some cases I just want to change the content or blank it out.
I guess we need the CREATE TABLE statement for the target table, the code, and the BCP format file to help on this one. If you can attach the txt file, that would help as well. Of course, make sure it doesn't have any private data in it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2010 at 6:09 am
Unfortunately, I cannot provide the input data as it is protected under HIPAA. I am sure that the data file itself is not the problem. The only thing I can think of that may be a problem is the format file. Here are a few lines from the beginning of that file. (the lines may show up wrapped here)
8.0
123
1SQLCHAR09""1Inv_numSQL_Latin1_General_Cp437_BIN
2SQLCHAR09""2Trip_numSQL_Latin1_General_Cp437_BIN
3SQLCHAR08""3Trip_DateSQL_Latin1_General_Cp437_BIN
4SQLCHAR010""4First_nameSQL_Latin1_General_Cp437_BIN
5SQLCHAR01""5Middle_iniSQL_Latin1_General_Cp437_BIN
I don't know if that helps.
July 21, 2010 at 6:24 am
I think I figured it out. The collation sequence I used in the format file did not match that of the database so I changed the collation sequence to match. The database collation is:
SQL_Latin1_General_CP1_CI_AS
It seems to have worked. I can now edit the data if I need to
July 21, 2010 at 11:56 pm
[p]Create an error log when using BCP utility. It may help you. I understand that you need increase the column size of your table.[/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
July 22, 2010 at 3:31 pm
bryan.duchesne (7/21/2010)
I think I figured it out. The collation sequence I used in the format file did not match that of the database so I changed the collation sequence to match. The database collation is:SQL_Latin1_General_CP1_CI_AS
It seems to have worked. I can now edit the data if I need to
I typically use "" for the collation sequence so that it defaults to the correct one. Glad you got it sussed and thanks for the feedback on that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply