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)
              INSERT remotetbl(a, b)
                  VALUES(1, 9), (2, 999), (3, 111)'
EXEC FZ.tempdb.sys.sp_executesql @sql


SELECT @sql = 'CREATE PROCEDURE remote_sp AS
                  SELECT a, convert(tinyint, b) AS b FROM remotetbl'
EXEC FZ.tempdb.sys.sp_executesql @sql

SELECT @sql = 'CREATE PROCEDURE remote_trycatch_sp AS
                  BEGIN TRY
                      SELECT a, convert(tinyint, b) AS b FROM remotetbl
                  END TRY
                  BEGIN CATCH
                     IF @@trancount > 0 ROLLBACK TRANSACTION
                     EXEC error_handler_sp
                     RETURN 999
                  END CATCH'
EXEC FZ.tempdb.sys.sp_executesql @sql

--------------------------------- Local executions ----------------------------------
EXEC FZ.tempdb.dbo.remote_sp
go
BEGIN TRY
   EXEC FZ.tempdb.dbo.remote_sp
END TRY
BEGIN CATCH
   EXEC error_handler_sp
END CATCH
go
EXEC FZ.tempdb.dbo.remote_trycatch_sp
go
SELECT a, b 
FROM   OPENQUERY(FZ, 'EXEC remote_sp')
go
SELECT a, b
FROM   OPENQUERY(FZ, 'EXEC remote_trycatch_sp')
---------------------------------------- Clean-up section ------------------------------
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