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)
cast(substring(inserted.ApplicationCustomPublicData, 4, 16) as bigint) MessageQueueId,
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!