Boring SQL server message errors
Last week, I created some scripts to move data from an old database to a new one. Some tables consisted of many fields and the old ones were not typed, that is, all fields where char, varchar, etc.
INSERT INTO targetTable
(
targetField1,
targetField2,
targetField3,
...
targetFieldN
)
SELECT targetField1,
targetField2,
targetField3,
...
targetFieldN
FROM sourceTable
When I run the script, which was quite similar to the sample below, I got errors like the ones below.
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to a column of data type int.
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Wouldn't these messages be nicer if they state field(s) name(s) like I rewrote below?
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to column '[column name here]' of data type int.
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated on column '[column name here]'.
The statement has been terminated.
Because of lack of column name on this message, I had spent a lot of time figuring out which field(s) were causing these errors. I would have spared a lot of time, if messages stated which field(s) I should take care of. If you know of other boring SQL server error message(s), drop a comment or trackback and let us know about.