Getting Last Id from a Cross Server Insert using SQL Server

Question

I am running a cross server insert

INSERT INTO server.database.dbo.table (Field) VALUES('test')

Afterward, I need to get the id of the last insert. However, when I run the the scope_identity function, I don't get the latest id from the foreign server.

SELECT @ID=SCOPE_IDENTITY()

How would I retrieve the last id from a cross server insert?

Answer 1

From http://msdn.microsoft.com/en-us/library/ms187342.aspx

The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

Answer 2

I'm pretty sure you're going to have to wrap the INSERT in a stored procedure on the remote linked server and have the stored procedure return the IDENTITY column value.

reference: http://msdn.microsoft.com/en-us/library/ms187342.aspx