USE tempdb
go
-- Sets up the linked server, in case you don't have in it place, or you 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
SET XACT_ABORT OFF
SET NOCOUNT ON

CREATE SYNONYM FZ_executesql FOR FZ.tempdb.sys.sp_executesql

CREATE TABLE DidWeContinue(IfSoWhere varchar(50))

CREATE TABLE PedrosDowry (object_id int NOT NULL,
                          name      sysname NOT NULL,
                          PRIMARY KEY (object_id, name)
)

BEGIN TRY
   BEGIN TRANSACTION

   INSERT PedrosDowry(object_id, name)
      EXEC FZ_executesql N'SELECT object_id, name FROM dbo.Andy'

   INSERT DidWeContinue (IfSoWhere) VALUES ('Yes, we jogged along')
END TRY
BEGIN CATCH
   IF xact_state() <> -1
      INSERT DidWeContinue (IfSoWhere) VALUES ('We ended up in CATCH handler')
   ELSE
      PRINT 'Entering CATCH handler with doomed transaction.'
   ; THROW
END CATCH
go
SELECT IfSoWhere FROM DidWeContinue
SELECT @@trancount AS trancount, COUNT(*) AS PedrosDowry FROM PedrosDowry
IF @@trancount > 0 COMMIT TRANSACTION
go
DROP SYNONYM FZ_executesql
DROP TABLE DidWeContinue, PedrosDowry