BCP right truncation error 1

[monkeylizard]

I have an export from a 3rd party application in a tab delimited format. The exported file is flat text, tab delimited, and was designed to be fairly generic for importing into a DB (SQL, DB2, Oracle, etc). One field in the export is text. According to the app's documentation, the destination field in the DB should be nvarchar(255). Apparently that same restriction on size is not in the app's export process.

The app came with some scripts (.bat) to load the export files to the SQL db via BCP commands. During some imports, I'm receiving String data, right truncation in the error logs and it skips the record(s) with values > 255 in length.

I really don't care about it truncating. That would be just fine in this case if it would truncate at 255, but it doesn't. It skips the record.

Here's the command their script executes:

bcp TLWEB.dbo.TLWEB_SESSION in C:\TL\Export\loading\BulkSession.txt %BCPOPTIONS% -S SERVER1 -e C:\TL\Export\error\BulkSession.txt -f C:\TL\Script\Session.fmt >> %LOGFILESES% 2>&1

I have also tried adding the -n option, but that didn't help. I didn't think it would.

My question is: Is there a way to force BCP to actually do the truncating instead of warning about the truncation?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[gmmastros]

Can you post the contents of this file: C:\TL\Script\Session.fmt

This is the format file used by the bcp command.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[monkeylizard]

9.0
4
1       SQLCHAR       0       32      "\t"     2     SESSION_KEY                SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       32      "\t"     3     HIT_KEY                    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       512     "\t"     4     NAME                       SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       1024    "\r\n"   5     VALUE                      SQL_Latin1_General_CP1_CI_AS

And I think that may have answered my question.

NAME is 256 in the database
VALUE is 512 in the database.

I have expanded both and will try it again.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

Problem solved....

We'll call that an undocumented feature of the app's export/import process. I suspect they expanded the field in the export file, but forgot to change the size in the schema creation script.

Thank you George.



Out of curiosity, is there a switch for the BCP command that would force the data to truncate to fit the destination field(s)?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[gmmastros]

Out of curiosity, is there a switch for the BCP command that would force the data to truncate to fit the destination field(s)?

Not that I am aware of, but I don't use BCP a whole lot either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom