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