USE tempdb go -- Sets up the linked server, in case you don't have it place, or want to test -- different servers. IF EXISTS (SELECT * FROM sys.servers WHERE name = 'FZ') EXEC sp_dropserver FZ, 'droplogins' --EXEC sp_addlinkedserver FZ, '', 'MSDASQL', @provstr = 'Driver={SQL Server};Trusted_connection=yes;Server=(local)' EXEC sp_addlinkedserver FZ, '', 'SQLNCLI', '.' EXEC sp_serveroption FZ, 'rpc out', true go --------------------------------- Remote objects ------------------------------------- DECLARE @sql nvarchar(MAX) SELECT @sql = 'CREATE TABLE remotetbl(a int NOT NULL PRIMARY KEY, b int NOT NULL)' EXEC FZ.tempdb.sys.sp_executesql @sql SELECT @sql = 'CREATE PROCEDURE remote_sp @a int, @b char(1) AS INSERT remotetbl(a, b) VALUES(@a, @b) INSERT remotetbl(a, b) VALUES(@a + @b, 0)' EXEC FZ.tempdb.sys.sp_executesql @sql SELECT @sql = 'CREATE PROCEDURE remote_trycatch_sp @a int, @b char(1) AS SET NOCOUNT ON BEGIN TRY INSERT remotetbl(a, b) VALUES(@a, @b) INSERT remotetbl(a, b) VALUES(@a + @b, 0) END TRY BEGIN CATCH -- PRINT ''The CATCH handler in remote_trycatch_sp was entered'' IF @@trancount > 0 ROLLBACK TRANSACTION -- ; THROW EXEC error_handler_sp RETURN 999 END CATCH' EXEC FZ.tempdb.sys.sp_executesql @sql go ------------------------------------ Local objects ------------------------------ CREATE PROCEDURE local_sp AS SET NOCOUNT ON SET XACT_ABORT OFF BEGIN TRY BEGIN TRANSACTION EXEC FZ.tempdb.dbo.remote_sp 1, '1' EXEC FZ.tempdb.dbo.remote_sp 1, '2' COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Entering CATCH handler in local_sp' IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp END CATCH go CREATE PROCEDURE local_sp2 AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION EXEC FZ.tempdb.dbo.remote_trycatch_sp 1, '1' EXEC FZ.tempdb.dbo.remote_trycatch_sp 1, '2' PRINT 'local_sp2 jogs along' COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Entering CATCH handler in local_sp2' IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp END CATCH go CREATE PROCEDURE local_retcode_ataterror_sp AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION DECLARE @ret int EXEC @ret = FZ.tempdb.dbo.remote_trycatch_sp 1, '1' SELECT @ret AS [@ret], @@error AS [@@error] EXEC @ret = FZ.tempdb.dbo.remote_trycatch_sp 1, '2' SELECT @ret AS [@ret], @@error AS [@@error] COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Entering CATCH handler in local_retcode_ataterror_sp' IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp END CATCH go CREATE PROCEDURE local_retcode_sp AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION DECLARE @ret int SELECT @ret = 112 EXEC @ret = FZ.tempdb.dbo.remote_trycatch_sp 1, '1' IF isnull(@ret, 1) <> 0 RAISERROR('remote_trycatch_sp failed. Return status %d', 16, 1, @ret) SELECT @ret = 112 EXEC @ret = FZ.tempdb.dbo.remote_trycatch_sp 1, '2' IF isnull(@ret, 1) <> 0 RAISERROR('remote_trycatch_sp failed. Return status %d', 16, 1, @ret) COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Entering CATCH handler in local_retcode_sp' IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp END CATCH go CREATE SYNONYM remote_trycatch FOR FZ.tempdb.dbo.remote_trycatch_sp go CREATE PROCEDURE local_synonym_sp AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION DECLARE @ret int SELECT @ret = 112 EXEC @ret = remote_trycatch 1, '1' IF isnull(@ret, 1) <> 0 RAISERROR('remote_trycatch_sp failed. Return status %d', 16, 1, @ret) SELECT @ret = 112 EXEC @ret = remote_trycatch 1, '2' IF isnull(@ret, 1) <> 0 RAISERROR('remote_trycatch_sp failed. Return status %d', 16, 1, @ret) COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Entering CATCH handler in local_synonym_sp' IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp END CATCH go --------------------------------------- Run the tests --------------------------------- -- Run the test. EXEC local_sp go -- Check the results. SELECT a, b FROM FZ.tempdb.dbo.remotetbl go ---------------------------------------- Clean-up section ------------------------------ DROP SYNONYM remote_trycatch DROP PROCEDURE local_sp, local_sp2, local_retcode_ataterror_sp, local_retcode_sp, local_synonym_sp DECLARE @sql nvarchar(MAX) SELECT @sql = 'DROP TABLE remotetbl' EXEC FZ.tempdb.sys.sp_executesql @sql SELECT @sql = 'DROP PROCEDURE remote_sp, remote_trycatch_sp' EXEC FZ.tempdb.sys.sp_executesql @sql