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:
Use Remote Collation=True
Lazy Schema Validation=False
Enable Promotion of Distributed Transaction=True