String or binary data would be truncated.

[Katy44]

I'm getting the above error when trying to load data from a text file into SQL Server 2000 database. All I can find is that I'm trying to load data that is too long for the field in, but this is using Bulk Insert with a format file, and the table structure was created to match, so why is this error coming up?

 

[tc3596]

Has the table structure changed at all?...i.e. identity column removed. This could be the first of a couple of errors if that were the case.

 

[Katy44]

Thanks for your reply. I don't think so, and besides the bulk insert works fine for other files using the same format file and doesn't throw that error.
I should mention that when I SET ANSI_WARNINGS OFF, it works fine. I am still looking into the data to see if anything has gone wrong, but there's nothing obvious.

 

[vongrunt]

Do you have some trailing spaces in text file (at the end of line, between fields)?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Katy44]

No, the text file's exact length (inc spaces and end of line characters) must be exactly divisible by the length of a standard record, and it is.