5

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
4
  • What are the linked server settings that you have currently ?
    – Kin Shah
    Commented Jul 26, 2013 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 Commented Jul 26, 2013 at 18:31
  • Can you try using sp_executesql with OUTPUT clause ?
    – Kin Shah
    Commented Jul 26, 2013 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. Commented Aug 3, 2013 at 14:35

1 Answer 1

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.

2
  • 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. Commented Aug 3, 2013 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
    Commented Jul 16, 2014 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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