Best way to get identity of inserted row in Linked server?

I am inserting record in a remote Sql Server using Linked server, Now I wanna 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

  • Unable to Connect C# Application to MSSQL
  • Microsoft SQL Server 2005 Console Write Line
  • SQL Azure - One session locking entire DB for Update and Insert
  • How can I populate my database's all tables with random data?
  • Any issues with using Windows Authentication for SSIS/SQL Server?
  • How to create an efficient database design in sql using asp.net?
  • Full join multiple tables on same columns
  • Get the last inserted row ID (with SQL statement)
  • Why am I getting “Unable to load the specified metadata resource” error?
  • Return Multi Row DataSet as Single Row CSV without Temp Table
  • How to SUBSTRING A Nvarchar Value From A Word To Another Word In Sql Server
  • SQL Job for Sending Email Notifications
  • 3 Solutions collect form web for “Best way to get identity of inserted row in Linked server?”

    You could use the remote side’s sp_executesql:

    DECLARE @ScopeIdentity (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');

    Yet another variation, in case linked user has permission to call procedures on linked server:

    DECLARE @ScopeIdentity int
    EXEC @ScopeIdentity = [linkedServerName].[database].[schema].sp_executesql N'
      INSERT INTO [table] ...
      SELECT SCOPE_IDENTITY()'
    

    try something like this:

    --when RemoteTable is (Rowid int identity(1,1) primary key, rowValue varchar(10))
    exec ('INSERT server.database.owner.RemoteTable (rowValue) VALUES (''wow'');select SCOPE_IDENTITY()')
    

    the EXEC will return a result set containing the SCOPE_IDENTITY() value

    if you have to do this for SQL Server 2005+ you can just add an OUTPUT INSERTED.IdentityColumn to get a result set of the identitie(s). Add an INTO onto that OUTPUT and you can store them in a table/table variable on the local machine.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.