BCP Utility

  • 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 ?

  • 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)

  • 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.

  • 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/

  • 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)

  • 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.

  • 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

  • [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

  • 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)

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

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