April 25, 2012 at 8:27 am
Hello,
we have some sp's which either has to run all at one in sequence or should not run. all the sp's are inside a main sp.
if there is an error it should send mail through DBMail.
kindly help how to achieve it succesfullt as it is not triggering mail properly.
Below is the sample code
CREATE Proc SPName
as
Begin
DECLARE @tableHTML NVARCHAR(300)
Begin tran SampleTran
exec SP1
exec SP2
exec SP3
exec SP4
exec SP5
if @@error = 0
Begin
commit tran SampleTran
End
else
Begin
rollback tran SampleTran
Select @tableHTML = ERROR_MESSAGE()
Email statement
End
End
Regards
Durai Nagarajan
April 25, 2012 at 8:40 am
@@Error checks for the error status of the last statement run. In this case EXEC SP5. Any errors thrown by any other procedure is ignored.
http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 9:10 am
Thanks, i know some how forgot while implementing it (stupid in that way).
good for showing a good route to write code as well.
Regards
Durai Nagarajan
April 25, 2012 at 1:33 pm
Here is a generic create proc template for you that implements TRY/CATCH for exception handling, as the article Gail provided alludes to as the preferred way to implement error handling in SQL 2005 and above:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'SchemaName.ProcedureName')
AND type IN (N'P', N'PC') )
DROP PROCEDURE SchemaName.ProcedureName;
GO
CREATE PROCEDURE SchemaName.ProcedureName
AS
BEGIN
/********************************************************************************
Created:
Purpose:
Author:
Called By:
Example:
Modification History:
Date Author Purpose
----------- --------------- ----------------------------------------------------
********************************************************************************/
SET NOCOUNT ON;
BEGIN TRY;
BEGIN TRAN;
-- do some stuff...
COMMIT TRAN;
END TRY
BEGIN CATCH;
IF XACT_STATE() != 0
ROLLBACK TRAN;
DECLARE @err_str VARCHAR(2048),
@err_sev INT,
@err_state INT;
SELECT @err_str = ERROR_MESSAGE(),
@err_sev = ERROR_SEVERITY(),
@err_state = ERROR_STATE();
RAISERROR(@err_str, @err_sev, @err_state);
END CATCH;
END;
GO
-- grant exec permissions here
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 2:02 pm
Do we have to check XACT_STATE() on catch
If it comes to catch wont that be definitely error.
Regards
Durai Nagarajan
April 25, 2012 at 2:04 pm
durai nagarajan (4/25/2012)
If it comes to catch wont that be definitely error.
Yes it will. Control only passes to the catch block when an error happens.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 2:06 pm
Yes. If the error was such that it left the transaction in a state where it cannot be rolled back, or another inner proc already rolled the transaction back, then you would not want to call ROLLBACK in your CATCH block.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 2:10 pm
opc.three (4/25/2012)
Yes. If the error was such that it left the transaction in a state where it cannot be rolled back
A transaction can never be in a state where it cannot be rolled back. It can be in a state where it can't be committed (doomed transaction), but rollback is always possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 2:11 pm
Thanks all
That makes me much clear
Regards
Durai Nagarajan
April 25, 2012 at 3:16 pm
GilaMonster (4/25/2012)
opc.three (4/25/2012)
Yes. If the error was such that it left the transaction in a state where it cannot be rolled backA transaction can never be in a state where it cannot be rolled back. It can be in a state where it can't be committed (doomed transaction), but rollback is always possible.
Not sure what you're pointing out...the transaction being the one opened in the TRY in the example. XACT_STATE() = 0 makes sure that the transaction can be rolled back, i.e. that XACT_ABORT was not ON when the error occurred or that an inner proc did not explicitly rollback the transaction.
My point is if you call commit or rollback when XACT_STATE() = 0 then another error would be generated, this time in your CATCH block where you cannot do much about it, so it is important to check XACT_STATE() before calling ROLLBACK.
Edit: strikethrough off-hand comment about XACT_ABORT being ON potentially affecting the ability to rollback a transaction
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 3:28 pm
opc.three (4/25/2012)
GilaMonster (4/25/2012)
opc.three (4/25/2012)
Yes. If the error was such that it left the transaction in a state where it cannot be rolled backA transaction can never be in a state where it cannot be rolled back. It can be in a state where it can't be committed (doomed transaction), but rollback is always possible.
Not sure what you're pointing out...the transaction being the one opened in the TRY in the example. XACT_STATE() = 0 makes sure that the transaction can be rolled back, i.e. that XACT_ABORT was not ON when the error occurred or that an inner proc did not explicitly rollback the transaction.
My point is if you call commit or rollback when XACT_STATE() = 0 then another error would be generated, this time in your CATCH block where you cannot do much about it, so it is important to check XACT_STATE() before calling ROLLBACK.
My point is that an error cannot leave a transaction in a state where it cannot be rolled back. (well, it can, but then rollback is the least of your concerns as the entire database will be suspect)
Now, if the transaction was never started, rolled back elsewhere or already committed (ie there is no transaction at all) and you call commit or rollback, yes that will give an error that there is no open transaction. That can be checked with @@TranCount or XACT_STATE()
The time you need to check XACT_STATE() to see what the error did to the transaction is when trying to commit after an error has been caught.
Edit: It's also worth noting that xact_abort on does not prevent you from rolling back the tran in the catch
create table test (id int not null promary key)
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
INSERT INTO dbo.Test VALUES (1)
INSERT INTO dbo.Test VALUES (1) -- pk violation
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT, XACT_STATE()
ROLLBACK
END CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 4:01 pm
GilaMonster (4/25/2012)
opc.three (4/25/2012)
GilaMonster (4/25/2012)
opc.three (4/25/2012)
Yes. If the error was such that it left the transaction in a state where it cannot be rolled backA transaction can never be in a state where it cannot be rolled back. It can be in a state where it can't be committed (doomed transaction), but rollback is always possible.
Not sure what you're pointing out...the transaction being the one opened in the TRY in the example. XACT_STATE() = 0 makes sure that the transaction can be rolled back, i.e. that XACT_ABORT was not ON when the error occurred or that an inner proc did not explicitly rollback the transaction.
My point is if you call commit or rollback when XACT_STATE() = 0 then another error would be generated, this time in your CATCH block where you cannot do much about it, so it is important to check XACT_STATE() before calling ROLLBACK.
My point is that an error cannot leave a transaction in a state where it cannot be rolled back. (well, it can, but then rollback is the least of your concerns as the entire database will be suspect)
I see what you picked up on now...I suspect it was my wording that was suspect there :hehe:
Now, if the transaction was never started, rolled back elsewhere or already committed (ie there is no transaction at all) and you call commit or rollback, yes that will give an error that there is no open transaction. That can be checked with @@TranCount or XACT_STATE()
Attempting a COMMIT after an error has occurred is a far less common scenario in my experience than what my template shows, but that is the niche XACT_STATE() occupies, i.e. what @@TRANCOUNT cannot give us, but its function obviously overlaps @@TRANCOUNT's.
The time you need to check XACT_STATE() to see what the error did to the transaction is when trying to commit after an error has been caught.
You are right though (as usual :~) checking @@TRANCOUNT > 0 would also be sufficient in place of XACT_STATE() != 0 to determine whether a ROLLBACK could safely be called in the CATCH block I showed...preference I suppose, but one or the other should be checked else an error could be raised by calling ROLLBACK when no transaction was active.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply