|
|
Posted by Erland Sommarskog on 09/28/05 11:33
Metal Dave (metal@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|