verbose error information for SQL Server bulk insert

Question

I'm using SQL Server Express 2008 and I'm doing a bulk insert of data. I'd like to have more verbose error messages, ideally printing the data that failed to be inserted. Is that possible?

Answer 1

It is possible, but it can require a lot of effort to to do this--I recall working on a subsystem for a few days before I got it to do everything it needed to do. I believe this is one of the (few but still too many) places where, upon hitting an error, SQL will return two (2) error messages back-to-back, the second message is vague and indistinct, and all the error handling functions can only access info pertaining to that second lame message, and not the first one where the real info is. I don't have the code in front of me, but the logic was something like:

• Use the "errorfile" option on BULK INSERT to generate an error file IF the bulk insert fails
• TRY/CATCH the bulk insert call, and carefully check the returned error number
• If the error is the appropriate type, open and read the contents of the file to determine what went wrong where, and build your error message around that

Awkward as anything, but ultimately it worked out pretty well. So long as the drive+path+filename you were inserting from didn't exceed 128 characters (in SQL 2005, and I just bet they didn't fix that in 2008.) I do not count Bulk Insert as one of my favorite commands.