September 25, 2011 at 3:13 am
Both examples you gave, the break in SSMS and a query timeout, are client-side events. My idea is that shouldn't then their consequences not be handled at the client side too? The client has (explicitly or by default of the connection) set the timeout period and is handed a signal (somehow) that a timeout occured, it is then the client side's responsibility to rollback the transaction that it knows it opened. On the other hand, the client may also decide to re-try the operation. If you have xact_abort ON set, that client will not have any way to know the transaction has been aborted by SQL server. I'd say it makes a lot more sense if the transaction were still open after the timeout, i.e. all the more reason to set xact_abort OFF.
But wait, I think I now see where you are coming from: the client will not know about a transaction started from inside a stored procedure it calls. It will thus not know to roll one back in case of a timeout. You are right, it does serve a very good purpose there to set xact_abort ON. It is because the client interferes in an asynchronous way with the execution of the T-SQL code. So am I correct to say then that xact_abort ON is not needed if the code is called without a timeout period set (which happens to be the case for service broker code)? Or are there any more side effects/situations that I have missed?
September 25, 2011 at 3:37 am
SQL Kiwi (9/23/2011)
R.P.Rozema (9/23/2011)
In a perfect world you are right, a transaction get started or it doesn't. But that's exactly my point here: if "begin transaction" fails, will the transaction state be consistent? Since "begin transaction" failed, we can't be sure.Hah. Well I'm all for defensive programming, but you might as well worry about what happens if the BEGIN TRY fails and leaves you with an open transaction. Seriously, I wouldn't worry about SQL Server failing to begin a transaction leaving you in some weird netherworld.
I fully agree with you and I don't worry about it, that's why I said there was a theoretical difference only. The only reason why I brought it up is as a clarification for why I prefer this order. I like my coding to be well-defined. Leaving the order of begin commit - begin try open for guessing does not fit that well-defined rule. So, in the absence of a practical reason to choose one option over the other I've used this theoretical reason to make the distinction.
I must say though that I have nowhere found any explicit documentation that begin transaction can not fail. Knowing that documentation on errors that can occur is missing for most statements in reference documents like BOL, the fact that there are no possible errors listed with the begin transaction statement doesn't mean that they can not occur. We may just not have encountered (or identified) the situations yet... But don't worry, I won't go as far as to assume that begin transaction, or begin try, for that matter, can fail (I wouldn't even know how to code for those situations ;-))
September 25, 2011 at 9:47 pm
BWAA-HAAA!!!! I found the ultimate solution to error handling years ago... don't write code that can make any mistakes. :-):-D:-P;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2011 at 6:44 am
[font="Verdana"]
Gianluca Sartori (9/23/2011)
Mahesh Bote (9/23/2011)
try this ...
Begin Try
Begin Tran
Insert Into TableName Values (99, 'ZZZ');
Update TableName
SetColName = 'XXX'
Where ColName = 99;
Commit Tran
End Try
Begin Catch
If @@Error <> 0
Begin
RollBack Tran
End
End Catch
If you fall into the catch block @@Error must be other than 0.
Am I missing something?
Sartori, got your point ... 🙂
Begin Catch
If xact_state() <> 0 -- @@Error <> 0
Begin
Rollback Transaction
End
End Catch
[/font]
MH-09-AM-8694
October 10, 2011 at 9:42 am
Using all your inputs and from what I have read on BOL, I have come up with the below code. Please see my questions below. Appreciate your help.
BEGIN TRY
BEGIN TRAN
-- Here I do a couple of inserts and deletes to 5 tables.
-- I have a table to track these transactions. So if all of them succeed I log a entry into the table by calling a stored proc which has a bit column which specifies True or False for the success of all the transactions.
-- Call the stored proc to enter True in the trackingtable
END TRAN
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
-- Call the stored proc to enter False in the trackingtable
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
QUESTIONS:
1. Am I doing it right?
2. Can I nest the try catch to check for insert into trackingtable too?
3. Is there a better way to do this?
October 12, 2011 at 12:41 pm
krishna.vanne (10/10/2011)
...1. Am I doing it right?
2. Can I nest the try catch to check for insert into trackingtable too?
3. Is there a better way to do this?
1 - No, you don't. The commit tran should be the last statement before the 'end try' (which is incorrectly written as 'end tran' in your example) inside the try block.
Furthermore you should check xact_state() instead of @@trancount, especially if you execute procedures from within the try block. Using xact_state() you can check for uncommittable transactions (which still count in @@trancount, but can not be continued on). So your example should look something like this:
BEGIN TRY
BEGIN TRANSACTION trnMyTransaction;
-- Here I do a couple of inserts and deletes to 5 tables.
-- I have a table to track these transactions. So if all of them succeed
-- I log a entry into the table by calling a stored proc which has a
-- bit column which specifies True or False for the success of all the
-- transactions.
-- Call the stored proc to enter True in the trackingtable.
-- You only get to here if all actions succeeded, so you don't need
-- to check for any conditions.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- First undo any changes made, restoring the
-- situation as to how it was before the transaction
-- was begun.
IF xact_state() > 0
-- Undo the named transaction.
ROLLBACK TRANSACTION trnMyTransaction;
ELSE IF xact_state() < 0
-- A doomed transaction does not have a name any more, so
-- we can only roll it back as an unnamed transaction.
ROLLBACK TRANSACTION;
-- Use error_message(), error_number() and the like to give
-- information on what went wrong.
-- Call the stored proc to enter False into the trackingtable
END CATCH
2 - yes, you can nest another try-catch within a try block.
3 - yes, there is. Put all your actions (5 inserts?) together into a stored procedure. This stored procedure starts it's own transaction and has it's own try-catch block to handle any errors raised from the 5 inserts. Then, outside that procedure handle logging the results. Here is an example:
-- An example stored procedure. This procedure does
-- what it needs to do, i.e. excluding any logging
-- etc. Your implementation will need some
-- parameters added and the code inside the try-block
-- needs to be completed: this is just an example
-- of how to write your procedure.
create procedure dbo.uspDo5Inserts
as
begin
set nocount on;
begin try
-- Start the transaction.
begin transaction trnDo5Inserts;
insert into dbo.Table1(...)
select ...
...
-- Commit the transaction if all actions
-- succeeded.
commit transaction trnDo5Inserts;
end try
begin catch
-- Any error (with error level 11 or above) inside
-- the try-block will make execution continue here.
-- First thing to do is to make sure all remnants of
-- the transaction are cleared: xact_state()
-- indicates the status of the transaction as it was
-- left after the error:
-- 1 - valid transaction,
-- 0 - no transaction,
-- -1 - doomed transaction.
if xact_state() > 0
rollback tran trnDo5Inserts;
else if xact_state() < 0
rollback tran;
-- Optionally you can go and collect some information
-- on what exactly went wrong. This would be the place
-- for some technical logging.
declare @msg varchar(2048),
@nr int;
select @msg = error_message(),
@nr = error_number();
-- Raise a new error with a functional description of
-- the action that could not be performed, what the
-- result of this failure is, how to continue, etc.
raiserror( 'Your changes could not be stored. Please retry. (%d:%s)', 16, 1, @nr, @msg);
end catch
return 0;
end;
-- And now how to call this stored procedure.
-- The call to the procedure is enclosed in another
-- try-catch block. And the logging is not
-- within the same transaction as the action
-- itself.
begin try
exec dbo.uspDo5Inserts;
exec dbo.uspLogOK 'Do 5 inserts';
end try
begin catch
declare @msg varchar(2048),
@nr int;
select @msg = error_message(),
@nr = error_number();
exec dbo.uspLogFailure @msg, @nr;
end catch
October 12, 2011 at 1:34 pm
Gianluca Sartori (9/23/2011)
You can use my stored procedure code template[/url] if you like it.It should do what you ask for.
Regards
Gianluca
Gina, Can you please post me a Template for handling error on CURSOR within a Procedure ?
Thanks
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 12, 2011 at 1:48 pm
SQL_By_Chance (10/12/2011)
Gianluca Sartori (9/23/2011)
You can use my stored procedure code template[/url] if you like it.It should do what you ask for.
Regards
Gianluca
Gina, Can you please post me a Template for handling error on CURSOR within a Procedure ?
Thanks
What do you mean "error on cursor"? An error that occurs within the loop? Nested transactions?
Expand a bit and I'll try to help.
-- Gianluca Sartori
October 12, 2011 at 1:57 pm
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 12, 2011 at 1:58 pm
SQL_By_Chance (10/12/2011)
What do you mean "error on cursor"? An error that occurs within the loop? Nested transactions?Expand a bit and I'll try to help.
Yeah the dynamic sql that is generated using CURSOR
BEGIN
BEGIN TRY
--- CODE
BEGIN TRY
SET @sql = '--- Some very long INSERT/UPDATE QUERY ---'
SP_EXECUTESQL @sql
END TRY
BEGIN CATCH
---
END CATCH
END TRY
FETCH NEXT ....
END TRY
BEGIN CATCH
--END CATCH
I want to handle code containing sp_executesql
PS: The CURSOR is within a Procedure
Thanks[/quote]
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 12, 2011 at 2:26 pm
You code sample doesn't make sense to me. Begin try / end try are not always matched.
Try indenting your code, so that it flows naturally even from a "visual" standpoint.
BEGIN
BEGIN TRY
--- CODE
BEGIN TRY
SET @sql = '--- Some very long INSERT/UPDATE QUERY ---'
SP_EXECUTESQL @sql
END TRY
BEGIN CATCH
---
END CATCH
END TRY
--Nothing can go between END TRY and BEGIN CATCH
--FETCH NEXT ....
--END TRY
BEGIN CATCH
END CATCH
So, what are you trying to do here?
-- Gianluca Sartori
October 13, 2011 at 12:14 am
CREATE PROCEDURE EXAMPLE (@parameter)
---- To illustrate how I intend to Use Execption Handling inside my Cursor
AS
BEGIN
BEGIN TRY
DECLARE @TABLENAME VARCHAR (10)
DECLARE DROPTABLE CURSOR
FOR SELECT NAME FROM INFORMATION_SCHEMA.TABLES -- OR SYSTABLES
WHERE NAME = @parameter ---- Just an example
OPEN DROP TABLE
FETCH NEXT FROM DROPTABLE
INTO @TABLENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
DECLARE @sql VARCHAR (500)
SET @sql = 'DROP TABLE ' + @TABLENAME
DBNAME.SP_EXECUTESQL @sql
END TRY
BEGIN CATCH
--- Capture error during execute @sql
END CATCH
FETCH NEXT FROM DROPTABLE
INTO @TABLENAME
END TRY
BEGIN CATCH
--- Capture error at proc level if any
--- Capture error in case type mismatch or something else
END CATCH
END
DEALLOCATE DROPTABLE
CLOSE DROPTABLE
Just want basic template. Did I answer you correctly ?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 18, 2011 at 12:35 pm
Thanks Rozema. Really helps. But I am little confused now.
In your script you have
IF xact_state() > 0
-- Undo the named transaction.
ROLLBACK TRANSACTION trnMyTransaction;
ELSE IF xact_state() < 0
-- A doomed transaction does not have a name any more, so
-- we can only roll it back as an unnamed transaction.
ROLLBACK TRANSACTION;
Why do we have to rollback the transaction in both cases. If its 1 it should be committed right?
Per books online,
USE AdventureWorks2008R2;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
November 30, 2011 at 8:58 am
All the DBA's or SQL gurus,
Can anyone give me an industry standard transaction error handling template. I know most of the syntax discussed till now works, but I want something that I can use as a standard template for all my transactions.
Thanks!
November 30, 2011 at 9:01 am
The first reply from Gianluca has just such a template...
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply