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
Best Answer
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.