INSERT INTO remote table on different server with OUTPUT clause gives error

Question

I have a procedure which makes one or two inserts into a remote table. To accomplish the second insert, I need to know the recently inserted Id of the first insert. To make this work my insert clause fascilitates the OUTPUT clause which let me know the Id of the first insert.

This works fine if the calling database (containing the procedure making the insert) and the receiver database resides on the same SQL Server, which is the case on my development environment. In the production environment, however, the calling database and the receiver database resides on different SQL Servers, which outputs the following error message:

Msg 405, Level 16, State 1, Procedure ProcedureName, Line LineNumber. A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Edit (the OUTPUT clause into temp table):

    declare @message table(MeddelandeKöId bigint primary key, MessageId bigint)
...
    output 
    cast(substring(inserted.ApplicationCustomPublicData, 4, 16) as bigint) MessageQueueId,
    inserted.Id MessageId 
    into @message

Which is the best approach to solve the current error? Should I make a simple select, disregarding the output statement, to get the recently inserted row or is there another better solution to this particular problem?

Thanks for your time!

Answer 1

(re-wrote, misunderstood)

If you want the remote ID, then the best way is to use a stored procedure on the remote server and return a result set.

Or don't redirect into a temp table/table variable. This isn't in scope on the remote server

See these on SO:

• https://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server/4537600#4537600
• https://stackoverflow.com/questions/2621887/sql-server-identity-issue/2621940#2621940