EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql