Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What are the linked server settings that you have currently ? –  Kin 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 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

1 Answer 1

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.

share|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 at 7:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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