tags:

views:

58

answers:

4
Q: 

Logging Stored Procedures Errors in a SQL Server 2005 Database

I'm using the general construct shown below for my TSQL stored procedures. This works fine for returning the error information to the calling application code. However, I would also like to log the error in the database itself. How can I accomplish this in SQL Server 2005?

BEGIN TRY

TSQL...

END TRY
BEGIN CATCH 
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

SELECT  @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE()

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR ( @ErrorMessage, -- Message text.
    @ErrorSeverity, -- Severity.
    @ErrorState -- State.
       )
END CATCH

SET NOCOUNT OFF
SET @Error = @@ERROR
RETURN @Error
A: 

Create an Errors table with columns (MessageText, Severity, State, DateTimeOccurred) and INSERT the information into it.

Mitch Wheat  2009-09-22 03:59:42
+2  A: 

Create an ErrorLog table and write to this in the CATCH block.

It's not as easy as that though...

However, you'll want to test for "@@TRANCOUNT = 0" first because it will be rolled back later, say if you nest stored procedures with TRY/CATCH or have a client transaction. This does not apply if you use SET XACT_ABORT ON

This means you may have multiple error messages if you have nesting, so I'd log both ERROR_PROCEDURE() and OBJECT_NAME(@@PROCID) too to distinguish both where the error happened and where it's being logged.

gbn  2009-09-22 04:06:50
+2  A: 

The simple answer is 'write to a log table', but actually this is a little bit more complicated because any table write you make is subject to the current transaction and catch blocks are subject to the error handling tri-state transactions. You must use XACT_STATE to check the current transaction state. If is a doomed transaction (state -1) then you must first rollback and then log, otherwise your attempt to insert into a log table will cause a batch abort.

For an example of a procedure template that uses T-SQL try/catch blocks and properly handles transactions see Exception handling and nested transactions.

Remus Rusanu  2009-09-22 04:08:35
A: 

here is an example of nested procedure logging with rollbacks...

this creates two test tables and three procedures that can be called in a nested manner with transactions:

if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureA') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureA
if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureB') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureB
if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureC') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureC
if exists (select * from sysobjects where id = object_id(N'YourLogTable') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table YourLogTable
if exists (select * from sysobjects where id = object_id(N'YourTestTable') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table YourTestTable

go
CREATE TABLE YourLogTable
(
     LogID    int               not null primary key identity(1,1)
    ,LogDate  datetime          not null default GETDATE()
    ,ProcedureName varchar(50)  null default OBJECT_NAME(@@PROCID)
    ,LogText varchar(8000)      not null
)
go
CREATE TABLE YourTestTable
(
     TestID    int not null primary key identity(1,1)
    ,TestData  varchar(100) not null
)

go
-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureA
(
    @ParamA1     int
   ,@ParamA2     varchar(10)
   ,@ErrorMsg    varchar(1000) OUTPUT
)
AS

DECLARE @LogValue      varchar(8000)
DECLARE @ReturnValueX  int
DECLARE @ErrorMsgX     varchar(1000)

DECLARE @TempValue     int

BEGIN TRY

    SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
        +': @ParamA1='+COALESCE(''''+CONVERT(varchar(10),@ParamA1)+'''','null')
        +', @ParamA2='+COALESCE(''''+@ParamA2+'''','null')


    BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,

    INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureA')



    --your logic logic here---
    IF @ParamA1=1
    BEGIN
        RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
    END

    SET @TempValue=@ParamA1/@ParamA1

    EXEC @ReturnValueX=ProcedureB @ParamA1,@ParamA2,@ErrorMsgX OUTPUT
    IF @ReturnValueX!=0
    BEGIN
        SET @ErrorMsg='Call to ProcedureB failed, ReturnValueX='+COALESCE(''''+CONVERT(varchar(10),@ReturnValueX)+'''','null')+', @ErrorMsgX='+COALESCE(''''+CONVERT(varchar(10),@ErrorMsgX)+'''','null')
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    --your logic logic here---
    INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureA')


END TRY
BEGIN CATCH

    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END

    SET @ErrorMsg=@LogValue+'; '
                   +CASE WHEN ERROR_NUMBER()     IS NOT NULL THEN 'Msg '         +CONVERT(varchar(30),   ERROR_NUMBER()     ) ELSE '' END
                   +CASE WHEN ERROR_SEVERITY()   IS NOT NULL THEN ', Level '     +CONVERT(varchar(30),   ERROR_SEVERITY()   ) ELSE '' END
                   +CASE WHEN ERROR_STATE()      IS NOT NULL THEN ', State '     +CONVERT(varchar(30),   ERROR_STATE()      ) ELSE '' END
                   +CASE WHEN ERROR_PROCEDURE()  IS NOT NULL THEN ', Procedure ' +                       ERROR_PROCEDURE()    ELSE '' END
                   +CASE WHEN ERROR_LINE()       IS NOT NULL THEN ', Line '      +CONVERT(varchar(30),   ERROR_LINE()       ) ELSE '' END
                   +CASE WHEN ERROR_MESSAGE()    IS NOT NULL THEN ', '           +                       ERROR_MESSAGE()      ELSE '' END
    INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)
    RETURN 999

END CATCH

IF XACT_STATE()!=0
BEGIN
    COMMIT
END

--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO

-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureB
(
    @ParamB1     int
   ,@ParamB2     varchar(10)
   ,@ErrorMsg    varchar(1000) OUTPUT
)
AS

DECLARE @LogValue      varchar(8000)
DECLARE @ReturnValueX  int
DECLARE @ErrorMsgX     varchar(1000)

DECLARE @TempValue     int

BEGIN TRY

    SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
        +': @ParamB1='+COALESCE(''''+CONVERT(varchar(10),@ParamB1)+'''','null')
        +', @ParamB2='+COALESCE(''''+@ParamB2+'''','null')

    BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,

    INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureB')

    --your logic logic here---
    IF @ParamB1=10
    BEGIN
        RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
    END

    SET @TempValue=@ParamB1/@ParamB1

    EXEC @ReturnValueX=ProcedureC @ParamB1,@ErrorMsgX OUTPUT
    IF @ReturnValueX!=0
    BEGIN
        SET @ErrorMsg='Call to ProcedureC failed, ReturnValueX='+COALESCE(''''+CONVERT(varchar(10),@ReturnValueX)+'''','null')+', @ErrorMsgX='+COALESCE(''''+CONVERT(varchar(10),@ErrorMsgX)+'''','null')
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    --your logic logic here---
    INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureB')


END TRY
BEGIN CATCH

    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END

    SET @ErrorMsg=@LogValue+'; '
                   +CASE WHEN ERROR_NUMBER()     IS NOT NULL THEN 'Msg '         +CONVERT(varchar(30),   ERROR_NUMBER()     ) ELSE '' END
                   +CASE WHEN ERROR_SEVERITY()   IS NOT NULL THEN ', Level '     +CONVERT(varchar(30),   ERROR_SEVERITY()   ) ELSE '' END
                   +CASE WHEN ERROR_STATE()      IS NOT NULL THEN ', State '     +CONVERT(varchar(30),   ERROR_STATE()      ) ELSE '' END
                   +CASE WHEN ERROR_PROCEDURE()  IS NOT NULL THEN ', Procedure ' +                       ERROR_PROCEDURE()    ELSE '' END
                   +CASE WHEN ERROR_LINE()       IS NOT NULL THEN ', Line '      +CONVERT(varchar(30),   ERROR_LINE()       ) ELSE '' END
                   +CASE WHEN ERROR_MESSAGE()    IS NOT NULL THEN ', '           +                       ERROR_MESSAGE()      ELSE '' END
    INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)

    RETURN 999

END CATCH

IF XACT_STATE()!=0
BEGIN
    COMMIT
END
--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO

-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureC
(
    @ParamC1     int
   ,@ErrorMsg    varchar(1000) OUTPUT
)
AS

DECLARE @LogValue      varchar(8000)
DECLARE @ReturnValueX  int
DECLARE @ErrorMsgX     varchar(1000)

DECLARE @TempValue     int


BEGIN TRY

    BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,

    SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
        +': @ParamC1='+COALESCE(''''+CONVERT(varchar(10),@ParamC1)+'''','null')

    --your logic logic here---
    INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureC')

    IF @ParamC1=100
    BEGIN
        RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
    END

    SET @TempValue=@ParamC1/@ParamC1


    --your logic logic here---
    INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureC')


END TRY
BEGIN CATCH

    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END

    SET @ErrorMsg=@LogValue+'; '
                   +CASE WHEN ERROR_NUMBER()     IS NOT NULL THEN 'Msg '         +CONVERT(varchar(30),   ERROR_NUMBER()     ) ELSE '' END
                   +CASE WHEN ERROR_SEVERITY()   IS NOT NULL THEN ', Level '     +CONVERT(varchar(30),   ERROR_SEVERITY()   ) ELSE '' END
                   +CASE WHEN ERROR_STATE()      IS NOT NULL THEN ', State '     +CONVERT(varchar(30),   ERROR_STATE()      ) ELSE '' END
                   +CASE WHEN ERROR_PROCEDURE()  IS NOT NULL THEN ', Procedure ' +                       ERROR_PROCEDURE()    ELSE '' END
                   +CASE WHEN ERROR_LINE()       IS NOT NULL THEN ', Line '      +CONVERT(varchar(30),   ERROR_LINE()       ) ELSE '' END
                   +CASE WHEN ERROR_MESSAGE()    IS NOT NULL THEN ', '           +                       ERROR_MESSAGE()      ELSE '' END
    INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)

    RETURN 999

END CATCH

IF XACT_STATE()!=0
BEGIN
    COMMIT
END
--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO

this will run the above procedures for four test cases:

  • fail in A, YourTestTable has no data in it, YourLogTable shows log info for A
  • fail in B, YourTestTable has no data in it, YourLogTable shows log info for B and A
  • fail in C, YourTestTable has no data in it, YourLogTable shows log info for C, B and A

  • all work, YourTestTable has data in it, YourLogTable has no data in it

    code here:

    DECLARE @ReturnValue  int
DECLARE @ErrorMsg     varchar(1000)


print '###########################################################################'
print 'will error out in A - should log A>>> EXEC ProcedureA 1,''abcd'''
delete from YourTestTable; delete from YourLogTable;
EXEC @ReturnValue=ProcedureA 1,'abcd',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable;
print '###########################################################################'
print 'will error out in B - should log B and A>>> EXEC ProcedureA 10,''abcd'''
delete from YourTestTable; delete from YourLogTable;
EXEC @ReturnValue=ProcedureA 10,'abcd',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable;
print '###########################################################################'
print 'will error out in C - should log C, B and A>>>> EXEC ProcedureA 100,''123'''
delete from YourTestTable; delete from YourLogTable;
EXEC @ReturnValue=ProcedureA 100,'123',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable;
print '###########################################################################'
print 'will complete, YourTestTable will contain data >>>> EXEC ProcedureA 2,''123'''
delete from YourTestTable; delete from YourLogTable;
EXEC @ReturnValue=ProcedureA 2,'123',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable;
print '###########################################################################'

here is the output, which shows a stack in the log, despite the rollbacks:

###########################################################################
will error out in A - should log A>>> EXEC ProcedureA 1,'abcd'

(6 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999          ProcedureA: @ParamA1='1', @ParamA2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureA, Line 33, testing, bad parameter

(1 row(s) affected)

YourTestTable TestID      TestData
------------- ----------- ----------------------------------------------------------------------------------------------------

(0 row(s) affected)

YourLogTable LogID       LogDate                 ProcedureName                                      LogText
------------ ----------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
YourLogTable 37          2009-09-08 15:04:39.663 ProcedureA                                         ProcedureA: @ParamA1='1', @ParamA2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureA, Line 33, testing, bad parameter

(1 row(s) affected)

###########################################################################
will error out in B - should log B and A>>> EXEC ProcedureA 10,'abcd'

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999          ProcedureA: @ParamA1='10', @ParamA2='abcd'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

(1 row(s) affected)

YourTestTable TestID      TestData
------------- ----------- ----------------------------------------------------------------------------------------------------

(0 row(s) affected)

YourLogTable LogID       LogDate                 ProcedureName                                      LogText
------------ ----------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
YourLogTable 38          2009-09-08 15:04:39.680 ProcedureB                                         ProcedureB: @ParamB1='10', @ParamB2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureB, Line 31, testing, bad parameter
YourLogTable 39          2009-09-08 15:04:39.680 ProcedureA                                         ProcedureA: @ParamA1='10', @ParamA2='abcd'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

(2 row(s) affected)

###########################################################################
will error out in C - should log C, B and A>>>> EXEC ProcedureA 100,'123'

(0 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999          ProcedureA: @ParamA1='100', @ParamA2='123'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

(1 row(s) affected)

YourTestTable TestID      TestData
------------- ----------- ----------------------------------------------------------------------------------------------------

(0 row(s) affected)

YourLogTable LogID       LogDate                 ProcedureName                                      LogText
------------ ----------- ----------------------- -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
KM  2009-09-22 12:21:34
Mixed try-catch logic *and* return error codes is not going to work on the long run. It is guaranteed that someone will forget to check a procedure return code and plow forward relying on the presence of a transaction that the calee had rolled back! It is arguably much better to rely on exceptions alone and handle all 3 states of XACT_STATE properly, by using save points, thus allowing the caller to decide if to rollback or continue on an alternate path in case of error. Many errors are recoverable. See http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/
Remus Rusanu  2009-09-22 22:10:56
Thank you for such a thorough example!
javacavaj  2009-09-23 02:13:24
@Remus Rusanu, we never use save points and always rollback, so the caller has few options since the transaction count will not match and it will go to the catch automatically.
KM  2009-09-23 13:17:15

related questions

Sql to query a dbs scheme
Transform Columns into Rows
SQL Client for Mac OS X that works with MS SQL Server
How do you get leading wildcard full-text searches to work in SQL Server?
SQL Server 2000: Is there a way to tell when a record was last modified?
What's the BEST way to remove the time portion of a datetime value (SQL Server)
I need to know how much disk space a table is using in SQL Server
What's the best way to determine if a temporary table exists in SQL Server?
Appropriate pagefile size for SQL Server
Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?
ASP.NET MVC "CRUD" Database Sample
How do I unit test persistence?
Best Book for a new Database Developer
Can I logically reorder columns in a table?
Best way to copy a database in SQL Server 2005/8?
Is Windows Server 2008 "Server Core" appropriate for a SQL Server instance?
Why doesn't SQL Full Text Indexing return results for words containing #?
Client collation and SQL Server 2005
Editing database records by multiple users
Deploying SQL Server Databases from Test to Live
SQL Server 2005 implementation of MySQL REPLACE INTO?
Upgrading SQL Server 6.5
How do I version my MS SQL database in SVN?
How to export data from SQL Server 2005 to MySQL
Check for changes to a SQL table?