How to get output from trigger on linked server 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
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.