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?


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 Out=True  
Use Remote Collation=True  
Collation Name=(blank)  
Connection Timeout=0  
Query Timeout=0  
Lazy Schema Validation=False  
Enable Promotion of Distributed Transaction=True
  • What are the linked server settings that you have currently ? – Kin Shah Jul 26 '13 at 17:06
  • @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 – Jason Litka Jul 26 '13 at 18:31
  • Can you try using sp_executesql with OUTPUT clause ? – Kin Shah Jul 26 '13 at 19:19
  • 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. – Jason Litka Aug 3 '13 at 14:35

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.

| improve this answer | |
  • 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. – Jason Litka Aug 3 '13 at 14:34
  • 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. – Brent Ozar Jul 16 '14 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.