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
--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
EXEC sp_serveroption FZ, 'query timeout', 5
go
--------------------------------- Remote objects -------------------------------------
go
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
go
--------------------------------- Local objects ------------------------------------------
CREATE PROCEDURE EatThatQuestion AS
SET NOCOUNT ON
SET XACT_ABORT OFF
BEGIN TRY
   -- BEGIN TRANSACTION
   INSERT FZ.tempdb.dbo.remotetbl (a, b) VALUES (1, 1)
   INSERT FZ.tempdb.dbo.remotetbl (a, b) VALUES (1, 1)
   -- COMMIT TRANSACTION
END TRY
BEGIN CATCH
   PRINT 'Entering CATCH handler in EatThatQuestion.'
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
END CATCH
go
CREATE PROCEDURE BlessedRelief AS
BEGIN TRY
   EXEC EatThatQuestion
END TRY
BEGIN CATCH
   PRINT 'This is the CATCH handler of Blessed Relief'
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
END CATCH
go
------------------------------------- The test -----------------------------------------------------
EXEC BlessedRelief
go
---------------------------------------- Clean-up section ------------------------------
DROP PROCEDURE BlessedRelief, EatThatQuestion
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'DROP TABLE remotetbl'
EXEC FZ.tempdb.sys.sp_executesql @sql