October 16, 2014 at 2:44 pm
I have a wrapper stored procedure ProcMain_wrapper that executes in one main dataabase and it calls child stored procedure in multiple databases. Does the rollback occur in all the databases if the stored procedure fails in any one of the multiple databases?
create procedure dbo.db_Main.ProcMain_wrapper (
declare @curClientCode as cursor ,@db varchar(10),@dbName varchar(20)
BEGIN TRANSACTION TRAN1
BEGIN TRY
SET @curClientCode = CURSOR FOR
SELECT [name] from sys.databases where name like 'dbclient_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SET @sqlStr = 'execute ' + @dbName + '.dbo.procChildClient_INSERT'
--print @sqlStr
exec (@sqlStr)
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
END TRY
BEGIN CATCH
if @@trancount> 0
ROLLBACK TRANSACTION TRAN1
END CATCH
if @@trancount > 0
COMMIT TRANSACTION TRAN1
GO
October 16, 2014 at 2:58 pm
If a transaction rolls back, all data modifications done within the transaction are rolled back. Even if they're in different databases.
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
October 17, 2014 at 5:00 pm
Yes, all modifications to recoverable resources for a single transaction must either all fail or all work, never some and not others. This is fundamental to how SQL Server (and many other RDBMSs) work. SQL therefore has internal process to absolutely insure that transactions act that way.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply