How to get output from trigger on linked server

Question

Fair warning, I know this isn't the best way to do this, but I'm trying to extend some existing code without needing to do a rewrite.

Anyway, I've got two SQL Server 2012 installs, one local in my office and another at a remote site. The server at the remote site has a linked server setup for my local system.

An application at the remote site inserts a record to a table on the linked server which has an INSTEAD OF trigger. That trigger has a select statement at the end which returns a single integer value. If I do the insert on the local box in SSMS I get the integer as output. If I do the insert on the remote box all I get is 1 row(s) affected.

Is there anyway I can get the trigger's output passed back to the linked server or am I going to need to rewrite as a stored procedure?

TEXT BELOW HERE IS BASED ON CLARIFICATION REQUESTED IN COMMENTS

All connections are made in the security context of a single user on the remote side. The output appears as expected if I use EXECUTE AS USER on the local server to do the insert rather than do it across the link, so it isn't a permission issue with the account.

Server options are:

Collation Compatible=False 
Data Access=True 
RPC=True 
RPC Out=True 
Use Remote Collation=True 
Collation Name=(blank) 
Connection Timeout=0 
Query Timeout=0 
Distributor=False 
Publisher=False 
Subscriber=False 
Lazy Schema Validation=False 
Enable Promotion of Distributed Transaction=True

• Kin Shah: What are the linked server settings that you have currently ?
• Jason Litka: @Kin All connections are made in the security context of a single user on the remote side. Server options are: Collation Compatible=False, Data Access=True, RPC=True, RPC Out=True, Use Remote Collation=True, Collation Name=(blank), Connection Timeout=0, Query Timeout=0, Distributer=False, Publisher=False, Subscriber=False, Lazy Schema Validation=False, Enable Promotion of Distributed Transaction=True
• Kin Shah: Can you try using sp_executesql with OUTPUT clause ?
• Jason Litka: I ended up just rewriting the code as a stored procedure. The more I reviewed the trigger code the less happy I was about just modifying it.

Answer 1

Have you thought about modifying your trigger to put that integer into a table? Include some identifying information, say the primary key from the table you are inserting into. Then just query the table after the insert.

• Jason Litka: The system doing the insert doesn't have an identifier, this process is what generates one. If a bunch of inserts happened at once there would be no way for the client to know which record in the outputmtable was theirs. I'll try that on Monday though so I can figure out if that works.
• Brent Ozar: Putting logging in the trigger is the way to go here, but the danger is setting up a big distributed transaction where the insert fires, plus the output table is read, all from a transaction done across servers. Recipe for concurrency problems there.