How do I rollback transactions inserted by stored procedures? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I rollback transactions inserted by stored procedures?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-13 : 19:22:38

Chris writes "I have a process that insert a row into 2 tables, each on different servers. The row is inserted on the remote server via stored procedure for various reasons. When the transaction fails only the row on the local server rolls back, never the row on the remote server. Here's my code:

BEGIN TRAN SyncItAll

/* Set entire transaction to fail if any runtime error occurs */
SET XACT_ABORT ON

/* Cram the new stuff into Tbl on Svr.DB */
Set Identity_Insert Db.Owner.Tbl On
INSERT INTO Db.owner.Tbl (Some columns....)
VALUES(Some values...)
SET @return_status = @@ERROR
IF (@return_status <> 0)
BEGIN
ROLLBACK TRAN SyncItAll
RETURN 1
END
Set Identity_Insert Db.Owner.Tbl Off

/* Jam the new stuff into Tbl on Svr.Db */
EXEC Svr.Db.Owner.StoredProcedure @id,@New_Guid
SET @return_status = @@ERROR
IF @return_status <> 0
BEGIN
ROLLBACK TRAN SyncItAll
RETURN 1
END

COMMIT TRAN SyncItAll

The row inserted by the 'EXEC Svr.Db.Owner.StoredProcedure @id,@New_Guid' line never gets rolled back. What am I doing wrong?

Thanks,
Chris"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources