Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

(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:

share|improve this answer
    
Thanks for the reply gbn, but that does not help. My current script inserts the output into a temp-table like this: output cast(substring(inserted.ApplicationCustomPublicData, 4, 16) as bigint) MessageQueueId, inserted.Id MessageId into @message. –  Benny Skogberg Oct 24 '11 at 7:40
    
@Benny Skogberg: I misunderstood. Don't use the table variable @ message –  gbn Oct 24 '11 at 7:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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