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
EXEC sp_serveroption FZ, 'query timeout', 5
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
      EXEC FZ.tempdb.dbo.remote_sp 1, '1'
      EXEC FZ.tempdb.dbo.remote_sp 1, '2'
   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
      EXEC FZ.tempdb.dbo.remote_trycatch_sp 1, '1'
      EXEC FZ.tempdb.dbo.remote_trycatch_sp 1, '2'
      PRINT 'local_sp2 jogs along'
   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
      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]
   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
      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)
   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
      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)
   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