sql-serversql-server-2000linked-server

Fetch identity of inserted row in Linked Server?

I am inserting record in a remote SQL Server using Linked Server, Now I want to get the id of inserted record. something like scope_identity() in local server. My remote SQL Server is 2000 version.

I have seen this post but I can't add any stored procedures in remote SQL Server.

Solution

  • You could use the remote side's sp_executesql:

    DECLARE @ScopeIdentity TABLE (ID int);
INSERT INTO @ScopeIdentity
EXEC server.master..sp_executesql N'
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY()';
SELECT * FROM @ScopeIdentity;

    Alternatively, you could use OPENQUERY:

    SELECT *
FROM OPENQUERY(server, '
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY() AS ID');