My Try / Catch / Begin Tran / Rollback tran question 1

[djj55]

Hello, SQL2008R2
Question one if you execute a TRUNCATE TABLE can this be rolled back?

In a Try/Catch if I include Raiseerror then do a Rollback with the error still work?

TRY CATCH
    BEGIN TRANSACTION My1;
    TRUNCATE TABLE MyTable;
    .
    .  My code here
    .
    
    COMMIT TRANSACTION My1;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

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

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

    ROLLBACK TRANSACTION My1;

END CATCH

Notice that the raiserror code is directly from BOL.
Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

You can rollback a truncate statement. See here:

http://sqlblog.com/blogs/denis_gobo/archive/2007/06/13/1458.aspx

I'm not 100% sure, but I think you may need to rollback the transaction before you raiserror. You should probably test this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you for the reply. It is early and I should have seen the obvious, move the rollback. Doh!

Next question. Will Raising an error in a catch also do a return? This is what I would expect.

Thank you again,



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[kjv1611]

I just wanted to say thanks for posting this question. This and BEGIN TRAN statements in general are 2 things that I'm trying to get myself accustomed to using - or at least thinking of times of when I should be using them.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[djj55]

Your welcome. As you can see I am learning. Most of our code was for SQL2000 so when we went to SQL2008R2 and I can use Try/Catch I do not think to use it unless there is a problem.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Rhys666]

I would very strong looking up and doing some reading around XACT_ABORT() and making use of XACT_STATE() in your catch block to determine the validity of rolling back a transaction as it is possible for the rollback itself to throw and error if it is determined that there is no transaction.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[djj55]

Rhys, thank you for the warning.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[flapeyre]

I use SQL Complete, and its template does it this way (with your code inserted):

BEGIN TRY
	BEGIN TRANSACTION My1;
		TRUNCATE TABLE MyTable;
	--.
	--.  My code here
	--.
	COMMIT TRANSACTION My1;
END TRY
BEGIN CATCH
	PRINT 'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
	', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
	', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
	', Line ' + CONVERT(VARCHAR(5), ERROR_LINE())
	PRINT ERROR_MESSAGE();

	IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION My1;
		END

END CATCH;

Note that this doesn't do a RAISERROR at all.




-- Francis
Francisus ego, sed non sum papa.

 

[djj55]

Thank you, Francis

I need the raiserror as we have a message log file that a failure notice was pushed to, but we did not know why as the SQL Job finish successfully.

I guess I could push the information to the message log, which would be similar to your method.

There are always several ways to do the same thing.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[flapeyre]

I guess I could push the information to the message log, which would be similar to your method.

There is an example of that here.

-- Francis
Francisus ego, sed non sum papa.

 

[gmmastros]

I would recommend you read this:

http://sqlfool.com/2008/12/error-handling-in-t-sql/

Basically, it shows an example of logging errors as well as how to construct the code with the error processing included.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[flapeyre]

Excellent, George. Thanks.

-- Francis
Francisus ego, sed non sum papa.

 

[Rhys666]

Please note, (based on my earlier post and the code in the provided link), although both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction @@TRANCOUNT cannot be used to determine whether that transaction has been classified as uncommittable and XACT_STATE cannot be used to determine whether there are nested transactions.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein