SSMS Bulk inserts = Error + Which line is it?

user_5504874 0
Viewed 328

I'm trying to insert a lot of data with SQL Server Management Studio. This is what I do:

  1. I open my file containing a lot of SQL inserts: data.sql
  2. I execute it (F5)

  3. I get a lot of these:

    (1 row(s) affected)

    and some of these:

    Msg 8152, Level 16, State 13, Line 26
String or binary data would be truncated.
The statement has been terminated.

Question: How to get the error line number ? Line 26 doesn't seems to be the correct error line number...

ssms sql-server
1 Answers

This is something that has annoyed SQL Server developers for years. Finally, with SQL Server 2017 CU12 w/ trace flag 460 they give you a better error message, like:

Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls

A method to get around this now is to add a print statement after each insert. Then, when you see your rows affected print out, you could see what ever you print.

...
insert into table1
select...

print 'table1 insert complete'

insert into table2
select...

print 'table2 insert complete'

This isn't going to tell you what column, but would narrow it down to the correct insert. You can also add SET NOCOUNT ON at the top of your script if you don't want the rows affected message printed out.

Another addition, if you really are using BULK INSERT and weren't just using the term generally, you can specify an ERRORFILE. This will log the row(s) which caused the error(s) in your BULK INSERT command. It's important to know that by default, BULK INSERT will complete if there are 10 errors or less. You can override this by specifying the MAXERRORS in your BULK INSERT command.

user_61678550
Related