none
Log to a table from within a stored procedure with transaction

    Question

  • I want to do the following:

    Execute a stored procedure that starts a transaction, and from within that stored procedure call another stored procedure that is outside transaction.

    The easiest to explain this is with a logging example (my situation is slightly different but it's the same principle)

    So, from within a transaction writing a log to another table. However, when my procedure fails, all logging is gone because the transaction is rolled back.

    The following example is an example (don't shoot me if it's not valid tsql)

    CREATE PROCEDURE usp_X
    AS
    
    BEGIN TRANSACTION
    INSERT INTO TABLE1 VALUES (NEWID(), 'abc')
    exec usp_LogEvent('record created'
    COMMIT TRANSACTION
    
    
    CREATE Procedure usp_LogEvent
    varchar(100) message
    AS
    INSERT INTO Logging VALUES (NEWID(), message)
    
    
    
    
    

    I know this can be achieved by using a TransactionScope object from within .NET, but that's not what I need.

    Is there any way to accomplish this using TSQL? (Try/Catch doesn't work for me either)

     

     

     


     

    Wednesday, October 06, 2010 11:02 AM

All replies

  • Hi ,

      You use TRY ..CATCH along with the transactions. In the catch block you execute the LOGGing related stored procedure after the ROLLBACK statement. So that log table data won't rollback.


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Wednesday, October 06, 2010 11:34 AM
  • Hi,

       This is draft code:

    CREATE PROCEDURE usp_X
    AS
    BEGIN TRY 
    BEGIN TRANSACTION
    	INSERT INTO TABLE1 VALUES (NEWID(), 'abc')
    	exec usp_LogEvent('record created')
    COMMIT TRANSACTION
    END TRY 
    
    BEGIN CATCH 
    	ROLLBACK
    	exec usp_LogEvent('record creatin failed')
    END CATCH 
    
    
    CREATE Procedure usp_LogEvent
    varchar(100) message
    AS
    INSERT INTO Logging VALUES (NEWID(), message)
    
    

    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Wednesday, October 06, 2010 11:36 AM
  • Which stored procedure fails usp_X or usp_LogEvent

    Please read Alex's articles to use try-begin catch block

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/13/your-try-block-may-fail-and-your-catch-block-may-be-bypassed.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi NModerator Wednesday, October 06, 2010 2:24 PM
    • Unproposed as answer by Joris Arits Monday, October 11, 2010 11:43 AM
    Wednesday, October 06, 2010 11:38 AM
    Answerer
  • Is there any way to accomplish this using TSQL? (Try/Catch doesn't work for me either)

    Consider logging the failed operation after the rollback:

    BEGIN TRY
    	BEGIN TRAN;
    	INSERT INTO dbo.TABLE1 VALUES (NEWID(), 'abc');
    	EXEC dbo.usp_LogEvent @message = 'record created';	
    	COMMIT TRAN;
    END TRY
    BEGIN CATCH
    	DECLARE
    		@ERRORNUMBER INT
    		,@ERRORMESSAGE NVARCHAR(2048)
    		,@ERRORSEVERITY INT
    		,@ERRORSTATE INT;
    	SELECT
    		@ERRORNUMBER = ERROR_NUMBER()
    		,@ERRORMESSAGE = N'Unexpected error %d: ' + ERROR_MESSAGE()
    		,@ERRORSEVERITY = ERROR_SEVERITY()
    		,@ERRORSTATE = ERROR_STATE();
    	IF @@TRANCOUNT > 0 ROLLBACK;
    	RAISERROR(@ERRORMESSAGE, @ERRORSEVERITY, @ERRORSTATE, @ERRORNUMBER);
    	EXEC dbo.usp_LogEvent @message = 'error creating record';	
    END CATCH
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Wednesday, October 06, 2010 11:43 AM corrected script
    Wednesday, October 06, 2010 11:42 AM
  • Like I said: Try/catch won't work for me...

    I'll try to explain the actual situation:

    There's 1 table in our database which contain the current ID's for all of our tables. This table has 2 columns, the keyname and a value.

     

    CREATE TABLE NextValueTable
    (
    keyname varchar(20),
    keyvalue int
    )
    

     

    Let's say I have a table

    CREATE TABLE Customers (
    cust_id int PRIMARY KEY ,<br/>
    cust_name
    )
    

    than the 'NextValueTable' contains a record:

    KeyName    KeyValue
    cust_id       22

    This is the case for all primary keys of our database.

    There is a stored procedure, which returns the new ID for a certain key, and increments it in the NextValueTable

    CREATE PROCEDURE GetNextKey
    	@Keyname varchar(25),
    	@NextKey int OUTPUT,
    	@NrOfKeys int = 1
    AS
    	--Gets the next key from the NextValTable
    	--When NrOfKeys is specified, the returned NextKey is the "CurrentId + 1", BUT the NextValTable is updated with the CurrentId + NrOfKeys
    	--This is intended for reserving for example 1000 keys at once, in stead of executing this stored procedure 1000 times
    
    	DECLARE @CurrentID int
    
    	BEGIN TRANSACTION GetKey
    
    	SELECT @CurrentID = keyvalue FROM nextValTable WHERE keyname = @keyname
    		
    	--Increment the key
    	SET @NextKey = @CurrentID + 1
    
    	--Reserve NrOfKeys
    	DECLARE @NewMaxKey int
    	SET @NewMaxKey = @CurrentID + @NrOfKeys
    
    	--Update the record with the new key
    	UPDATE nextValTable SET keyvalue = @NewMaxKey WHERE keyname = @Keyname
    
    	COMMIT TRANSACTION GetKey
    

    When inserting a value into Customers, I have to get new key from the stored procedure 'GetNextKey' and insert into customers. As a result, the NextValueTable is updated:

    KeyName    KeyValue
    cust_id       23

    The problem is, when a stored procedure with a long running transaction has to insert records into tables, the NextValueTable is locked for all other processes .

    Therefor, the stored procedure GetNextKey should be executed outside of the transaction, to avoid locking...

    Please don't provide answers like 'use uniqueidentifiers or a proper AutoIncrement' because I'm not allowed to do so.

    Wednesday, October 06, 2010 12:01 PM