locked
Use Pure TSQL to implement Autonomous Transaction RRS feed

  • Question

  • hi folks:

      I need to implement an autonomous transaction in our env.  After doing research, I understand there are a couple of options:

    Linked server loopback, CLR and table variables.. I want to use table variables as linked server and CLR both need to go through lengthy approval process in my production env as new objects will be introduced.  I have attached my code here. 

    The tricky part is that I have two SPs instead of One and what I want to achieve is that when OuterSP calls InnerSP, InnerSP

    needs to write error info to the log table.   I understand the idea of using table variable is that it is not controlled by Transaction which is good as I could preserve these values after Transaction  complets (rollback or commit) and write to log table in the OuterSP. The challenging part is that how to transport table variable that is captured inside InnerSP  to OuterSP  ? 

    Thanks

    set nocount on
    CREATE TABLE [dbo].[ErrorLog](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[err_msg] [varchar](500) NULL,
    	[err_proc] [varchar](500) NULL
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    CREATE PROCEDURE [dbo].[usp_WriteToLog]
    
      @msg VARCHAR(500),
      @proc VARCHAR(500)
    
    AS
    
    BEGIN
      Select 'In Write to log'
    
       set xact_abort on
      
      begin try
      Select 'In Write to log try'
    
      begin tran t1
      Select 'In Write to log try2'
    
       INSERT INTO dbo.ErrorLog (err_msg, err_proc)
       VALUES(@msg, @proc)
       
       commit tran t1
    
       end try
    
       begin catch
      Select 'In Write to log catch'
    
         rollback tran t1
      Select 'In Write to log catch2'
    
       end catch
    
    
    END
    GO
    
    CREATE procedure [dbo].[InnerSp]
    as
    
    BEGIN
      
      
      set xact_abort on
    
      BEGIN TRY
        select 'in InnerSP'
    	select 1/0
      END TRY
    
      BEGIN CATCH
      DECLARE @err VARCHAR(500)
      DECLARE @err_proc VARCHAR(500)
      SET @err	= ERROR_MESSAGE ()
      SET @err_proc = ERROR_PROCEDURE()
      Select 'In InnerSP catch'
    
      EXEC dbo.usp_WriteToLog @err, @err_proc
      Select 'In InnerSP catch and value has been written to ErrorLog', * From ErrorLog
    
      RETURN 1
    
      END CATCh
    
    
      RETURN 0
      
    
    END
    
    
    GO
    
    
    CREATE procedure [dbo].[OuterSP]
    as
    
    begin
    	set xact_abort on
    	begin try
    	   begin tran
    	      execute dbo.InnerSp
    	  
    	   commit tran
    
        end try
    
    
    	begin catch
    	  Select 'In Outer proc', @@TRANCOUNT 
    	
    	if @@trancount > 0
    	  begin
    	    rollback tran
    
    		return 1
    
    		end
    
    	  end catch
    
    
    
    
    end
    GO
    
    
    


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, March 18, 2016 8:13 PM

Answers

  • hi Eric, thanks for your help. I see you have modified my SPs so that the writeToLog sp has been moved to OuterSP instead of InnerSP.  

      When I copy and past your code into my test env, I did see the error log has been populated. However, that's not what I want to see.  

     Your solution did inspire me for another solution : 

    The innerSP  will contain  OUTPUT parameters , when it was called in the OuterSP, it  returns with either success or failure information  stored in variables.  I then insert that piece of info into Log table inside OuterSP. While Transaction was rolled back, variables  get value preserved. 

    thanks


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    • Proposed as answer by Eric__Zhang Saturday, April 2, 2016 3:24 PM
    • Marked as answer by Eric__Zhang Wednesday, April 6, 2016 8:18 AM
    Monday, March 21, 2016 11:09 PM

All replies

  • Sorry, table variables only works for this purpose inside a stored procedure. If you want something generic, it gets very difficult.

    There is SET CONTEXT_INFO which permits you to set 128 bytes which you later can pick up with the context_info function, but 128 is not a lot. And context info is often used to hold real user names when logins are made with a service account. (Although this improves in SQL 2016 with the introdction of session_context.)

    I think you will need bite the bullet and work with the red tape to get the CLR solution going.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, March 18, 2016 11:16 PM
  • Hi cat_ca,

    What is your SQL Server version? If 2012, you can try the THROW

    ALTER PROCEDURE [dbo].[InnerSp]
    AS
    BEGIN
    	SET XACT_ABORT ON
    
    	BEGIN TRY
    		SELECT 'in InnerSP'
    
    		SELECT 1 / 0
    	END TRY
    
    	BEGIN CATCH
    		
    
    		SELECT 'In InnerSP catch and value has been written to ErrorLog'
    			,*
    		FROM ErrorLog;
    		THROW;
    
    		RETURN 1
    	END CATCH
    
    	RETURN 0
    END
    GO
    
    ALTER PROCEDURE [dbo].[OuterSP]
    AS
    BEGIN
    	SET XACT_ABORT ON
    
    	BEGIN TRY
    		BEGIN TRAN
    
    		EXECUTE dbo.InnerSp
    
    		COMMIT TRAN
    	END TRY
    
    	BEGIN CATCH
    		SELECT 'In Outer proc'
    			,@@TRANCOUNT
    
    		IF @@trancount > 0
    		BEGIN
    			ROLLBACK TRAN
    
    		DECLARE @err VARCHAR(500)
    		DECLARE @err_proc VARCHAR(500)
    
    		SET @err = ERROR_MESSAGE()
    		SET @err_proc = ERROR_PROCEDURE()
    
    		SELECT @err,@err_proc
    		EXEC dbo.usp_WriteToLog @err
    			,@err_proc
    			RETURN 1
    		END
    	END CATCH
    END
    GO
    
    EXEC OuterSP
    
    SELECT * FROM ErrorLog



    Eric Zhang
    TechNet Community Support

    Monday, March 21, 2016 10:00 AM
  • What is your SQL Server version? If 2012, you can try the THROW

    But ;THROW is not going to help if you want to write to a log table inside a transaction and keep the data on rollback.
    ;THROW also has its own set of problems, as I discuss in my articles on error and transaction handling, and is best avoided.

    Monday, March 21, 2016 10:40 PM
  • hi Eric, thanks for your help. I see you have modified my SPs so that the writeToLog sp has been moved to OuterSP instead of InnerSP.  

      When I copy and past your code into my test env, I did see the error log has been populated. However, that's not what I want to see.  

     Your solution did inspire me for another solution : 

    The innerSP  will contain  OUTPUT parameters , when it was called in the OuterSP, it  returns with either success or failure information  stored in variables.  I then insert that piece of info into Log table inside OuterSP. While Transaction was rolled back, variables  get value preserved. 

    thanks


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    • Proposed as answer by Eric__Zhang Saturday, April 2, 2016 3:24 PM
    • Marked as answer by Eric__Zhang Wednesday, April 6, 2016 8:18 AM
    Monday, March 21, 2016 11:09 PM
  • The innerSP  will contain  OUTPUT parameters , when it was called in the OuterSP, it  returns with either success or failure information  stored in variables.  I then insert that piece of info into Log table inside OuterSP. While Transaction was rolled back, variables  get value preserved. 

    Note that for this to work, you cannot re-raise the error to have it caught by an outer CATCH handler, because in that case OUTPUT variables are not populated. (The semantics in SQL Server is copy-in, copy-out.)

    This means that you have to check return values which is tedious. Not talking of all those output variables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, March 22, 2016 11:04 PM