August 29, 2017 at 10:40 pm
I have posted a similar question few days, i thought i got it but after i made code changes things broke again , so here i am again :).
Below is pseudo code of what i am trying to do. I have few things to accomplish:
i) If execution of dbo.innerproc fails for a given value it should rollback.
ii) If there is an error in dbo.innerproc it should move on to next value.
When i execute exec outerproc i get below error when there is violation in primary key, however it is able to log for errors like where table doesn't exist and moves along to next one. Not sure what am i missing here.
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."
CREATE PROCEDURE dbo.innerproc
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
SET @query = ' Insert into db1.dbo.sometable select from someother table
Delete from db2.dbo.sometable';
SET @sql = '
UPDATE dbo.logging
SET errmsg = Error_message(),
severity = Error_severity(),
state = Error_state(),
errornumber = Error_number(),
session_id = @@spid,
status = 2,
level = 1,
processendtime = Getdate();
Begin try
BEgin transaction'
+ @query +
'Commit Transaction
End try
Begin Catch
Rollback transaction
UPDATE dbo.logging
SET errmsg = Error_message(),
severity = Error_severity(),
state = Error_state(),
errornumber = Error_number(),
session_id = @@spid,
status = 2,
level = 1,
processendtime = Getdate()
End Catch
';
END;
CREATE PROCEDURE dbo.Outerproc
AS
BEGIN
SET nocount ON;
SET xact_abort ON;
DECLARE @sql NVARCHAR(max);
SET @sql = 'exec dbo.innerproc';
BEGIN try
BEGIN TRAN;
EXEC (@sql);
COMMIT TRAN;
END try
BEGIN catch
IF ( @@TRANCOUNT > 0 )
BEGIN
ROLLBACK TRAN;
END;
UPDATE dbo.logging
SET errmsg = Error_message(),
severity = Error_severity(),
state = Error_state(),
errornumber = Error_number(),
session_id = @@spid,
status = 2,
level = 1,
processendtime = Getdate();
END catch;
END;
August 30, 2017 at 2:19 am
Have a read through these, see if they help.
The main problem with what you're doing is that SQL does NOT have nested transactions.
http://www.sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
http://www.sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
http://www.sqlinthewild.co.za/index.php/2015/11/17/savepoints-and-conditional-transactions/
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply