|
|
Posted by Erland Sommarskog on 10/02/48 11:55
Ted (r.ted.byers@rogers.com) writes:
> I have BULK INSERT T-SQL statements that work for all of my basic data
> tables except for one.
>
> Here is the problem statement (with obvious replacement of irrelevant
> path info):
>
> BULK INSERT igbconts_tmp FROM 'C:\\my_code_path\\IGBCONTS.txt'
> WITH (KEEPNULLS,
> FORMATFILE = 'C:\\my_data_path\\contacts.fmt');
>
> And here is the output from this statement:
>
> Msg 8152, Level 16, State 14, Line 3
> String or binary data would be truncated.
> The statement has been terminated.
>
> (0 row(s) affected)
>
> This tells me precisely nothing about where the real problem lies.
Well, the real problem is one of two:
1) The data file contains occasional fields that are longer that
the receiving columns in the database.
2) There is a mismatch between the data file and the format file
somewhere, so the fields get out of sync with the data in the
table.
You can decide which of these cases you have by running the command
SET ANSI_WARNINGS OFF before you do the bulk insert. This legacy
setting turns of the check for truncation. Then do a SELECT on the
table, and if the data looks OK, then it's the first alternative. And
if it's a mess, it's the second.
For the first situation, if you want to track down where the errors
are, you have to turn to BCP and use the -e argument to specify an
error file. Yes, you can specify an error file with BULK INSERT too,
but BULK INSERT and BCP behaves differently in this case. BULK
INSERT just aborts, and writes nothing to the error file. BCP
imports the rest of the rows, and writes a message to the error file
so you can see which are the problematic records in the file.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|