Try / Catch multiple errors

Transact-SQL
1

i have a text file, that when i bulk insert, it has a SUB character at the end of the file, so the bulk insert throws an unexpected end of file error. i'm trying to trap that type of error specifically on my import script, but when i do, the try/catch actually reports back the last error..

if i just run:

bulk insert [APPEND].[dbo].[grp 13_fixed_109] from 'c:\myfile.csv' with (datafiletype='char',fieldterminator=',',rowterminator='\n',firstrow = 2)

i get this for errors:
Msg 4832, Level 16, State 1, Line 17
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 17
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 17
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

is there any way to get all 3 error codes? or just the raw text of the error message (stored into a var)

if i use a try/catch like this:


begin try
	bulk insert [APPEND].[dbo].[grp 13_fixed_109] from 'c:\myfile.csv' with (datafiletype='char',fieldterminator=',',rowterminator='\n',firstrow = 2)
end try
begin catch
	print error_message()
end catch

i just get the last error in the sequence:
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

2

I think :slightly_smiling: if your just THROW the error in your CATCH block you will get all the errors. Kinda defeats the object of having a CATCH though ...

(You may need a ";" before and after the THROW)

3

from what i've seen, the THROW causes it to actually error, i dont want that, i want to store the error to something i can parse and then error handle. that, or i'd like to just capture all error codes, then parse that. either way. :frowning:

4

Indeed, which is why I think it defeats the object of having the CATCH. Unless sometime "upstream" can then handle the error (I suspect only an APP, rather than an SProc, is going to be able to do that)