I have created a stored procedure that does a BULK IMPORT. I wanted to add a bit of fault checking and added a BEGIN TRY and a BEGIN CATCH so that it sends an email with the error message if the T-SQL fails. The problem is that the BEGIN CATCH was being skipped and the procedure stops processing with the below error message.

Msg 4860, Level 16, State 1, Line 2
Cannot bulk load. The file "C:\temp\fileimport.txt" does not exist.

To fix the issue what I did is to add the BULK INSERT command in a variable and execute it. This way, the BEGIN CATCH is not ignored or skipped.

Fix below

BEGIN TRY
DECLARE @bulkimport varchar(1000)
SET @bulkimport = 'BULK INSERT mytable FROM ''C:\temp\fileimport.txt''
WITH (FIELDTERMINATOR = ''*'', ROWTERMINATOR = ''\n'')'
EXECUTE (@bulkimport)
END TRY

BEGIN CATCH
SELECT error_message()
END CATCH