BCP Utility

Question

Post reply

BCP Utility

bryan.duchesne

Ten Centuries

Points: 1134
More actions
July 18, 2010 at 11:22 am

#144011

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 ?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004654 More actions · Answer 1

Do the changes you're making make some data wider than the column that it was intended for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 2

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.

Mike01 SSChampion Points: 11420 More actions · Answer 3

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/

Jeff Moden SSC Guru Points: 1004654 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 5

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.

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 6

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

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 7

[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

Jeff Moden SSC Guru Points: 1004654 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)