using OUTPUT on remote server

Question

Post reply

using OUTPUT on remote server

ramadesai108

SSChampion

Points: 10609
More actions
February 26, 2013 at 1:04 pm

#271004

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

Output it to a local table with and move it from there?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Are you inserting to a remote table and trying to log it locally?

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

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.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

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.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

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.