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
add comment

1 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.

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
add comment

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.