Search code examples
sqlsql-serverbcp

Keep getting import error "String data, right truncation" using BCP

I've hit a problem. I'm trying to use BCP to import about 700,000 records into a working table.

This is the SQL I use to build my working table:

If Exists (Select * From sys.tables Where [name] = 'InactiveIDs')
    Begin 
        Drop Table [dbo].[InactiveIDs]
    End

Create Table [dbo].[InactiveIDs] (
    ContactId UniqueIdentifier Not Null,
    ID Varchar(50),
    EmailAddress VarChar(255) Not Null
)

Create Index IX_ContactIdEmailAddress
    On [dbo].[InactiveIDs] (ContactId, EmailAddress)

Here is the format file file:

13.0
3
1       SQLUNIQUEID         1       36      ","        1     ContactId                    ""
2       SQLCHAR             2       50      ","        2     ID                           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             2       255     "\r\n"     3     EmailAddress                 SQL_Latin1_General_CP1_CI_AS

Here is some sample data:

CBD60121-C5E1-E511-B2B4-005056820129,199e6799e3c64b06a87e86a5047e5f41,[email protected]
3D22A4C2-507B-E411-99C7-005056820126,76410ce5beab4a7da943b95b3de3b0c1,[email protected]
AE5B9335-B126-E611-ABF2-005056820020,e7d181abdf154f79b5dcaa4d64fec7f7,[email protected]
93F94F65-FA2A-E311-87A7-005056B5025F,3e2fae28cace4f068fa670879d7807e3,[email protected]
0A41305C-C087-E411-A37D-00505682001E,55b0162742b04a369c1c57d8d917d45c,[email protected]

Here's my command line:

bcp.exe dbo.InactiveIDs in InactiveIDs.csv -f InactiveIDs.bcp.fmt -T -S SqlServerName -d DatabaseName

Here is the output:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

BCP copy in failed
Solution

  • The official Microsoft doc explains what a Non-XML Format File consist of. I see 2 possible problems with yours, both with the first field:
    1. Column 4 (Host file data length) should be 37, if you open the page that explains each data type and you go to uniqueidentifier, it says 37 - now I copy the values from your sample data till the first comma to Notepad and they are 37 characters long.
    2. Maybe the type SQLUNIQUEID is also wrong and should be SQLCHAR. The doc says for columns 2 & 3 that these should be the same as the ones used to do the extract. I don't know if the extract was done with native data type but it seems to me that the first field is also text. So if point 1. modification still fails I would try SQLCHAR with a Prefix length of zero for all fields.