Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using OUTPUT on remote server Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 1:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
I have the following:

INSERT INTO TableB
VALUES(COL1, COL2,....)
OUTPUT INSERTED.COL1 INTO AUDITTable(COL1)
SELECT COL1,COL2,....
FROM TABLEA

This is giving me error:
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

I know that OUTPUT cannot work on remote tables.
So what is the alternative to this?

Thanks.
Post #1424227
Posted Tuesday, February 26, 2013 4:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
Output it to a local table with and move it from there?
Post #1424278
Posted Tuesday, February 26, 2013 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:37 PM
Points: 33,189, Visits: 15,329
Are you inserting to a remote table and trying to log it locally?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1424287
Posted Wednesday, February 27, 2013 1:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Yes, I am inserting into a remote table. Is there any other option besides selecting the record again and then inserting into an audit table?

Thanks.
Post #1424731
Posted Wednesday, February 27, 2013 2:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
You should be able to OUTPUT to another table as long as it's on the remote server as well, because the OUTPUT sees that table as local.
Post #1424746
Posted Wednesday, February 27, 2013 2:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:37 PM
Points: 33,189, Visits: 15,329
You could call a remote stored proc and return values back. That might allow you to use OUTPUT on the remote side, and then when it returns, insert those into an auditing table.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1424747
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse