error handling and bulk insert

[Metal Dave]

I do not understand the error handling of SQL Server here. Any error in
bulk insert seems to halt the current T-SQL statement entirely, rendering
it impossible to log an error. The first statement below executes as
expected, and were I to replace "print" with something meaningful I could
do some useful error handling. The second statement just seems to totally
bail out after the error, preventing me from doing any useful error
handling. This is a problem b/c I would like to schedule bulk inserts and
need to be notified if there is a problem.

The following can be run in QA to demonstrate:

print 'BEFORE TYPICAL ERROR'
raiserror('Some Error', 16, 10)
if (@@ERROR <> 0) print 'I can catch and log this error - good!' else
print 'I can not catch and log this error - bad!'
print 'AFTER TYPICAL ERROR'
go

print 'BEFORE BULK INSERT'
Bulk insert Northwind.dbo.orders
from 'ThisFileDoesNotExist'
if (@@ERROR <> 0) print 'I can catch and log this error - good!' else
print 'I can not catch and log this error - bad!'
print 'AFTER BULK INSERT'
go
TIA,
Dave

[Erland Sommarskog]

Metal Dave (me***@spam.spam) writes:

I do not understand the error handling of SQL Server here. Any error in
bulk insert seems to halt the current T-SQL statement entirely, rendering
it impossible to log an error. The first statement below executes as
expected, and were I to replace "print" with something meaningful I could
do some useful error handling. The second statement just seems to totally
bail out after the error, preventing me from doing any useful error
handling. This is a problem b/c I would like to schedule bulk inserts and
need to be notified if there is a problem.

That's the way error handling in SQL 2000 works. Some errors terminates
the current statment only. Others aborts the batch and rolls back the
current transaction. Compilation error terminates the current scope.
The net effect is that you cannot trap all errors in T-SQL.

For a longer discussion on the topic, I have two articles on on my
web site: http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html

In SQL 2005 the situation is entirely different as you can set up an
error handler with TRY CATCH.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx