CODEWITHSUNDEEP
sql-serverlinked-listidentity
Mohamad
Mohamad

Reputation: 1117

get @@Identity from another server(linked server)

I have a linked server, I want to add a record to the table on the linked server, Is it possible take @@identity from another server with linked server? (SQL Server 2005)

Upvotes: 6

Views: 11003

Answers (2)

Rafael Merlin
Rafael Merlin

Reputation: 2767

Using SQL Server 2012 here.

I've tried gbn method but I got this error:

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Besides that, I needed to add the SET XACT_ABORT ON; before my statement as I was using Distributed Transactions.

So I ended up solving both the issues by doing this:

BEGIN DISTRIBUTED TRANSACTION

SELECT idcolumn
FROM OPENQUERY(MyRemoteServer, '
  SET XACT_ABORT ON;
  INSERT INTO MyRemoteBD.dbo.SomeTable(col1,col2, ...) 
  OUTPUT INSERTED.idcolumn
  VALUES (val1,val2, ...);
  SET XACT_ABORT OFF');

COMMIT

And I was able to run this in a Distributed Transaction while getting the Identity from my insert.

A weird behavior about this statement is that if I run the Insert statement without opening the Distributed Transaction previously (must be the distributed one) it will return the next Identity but it will not insert anything. I have no idea why this is happening.

Edit:

Tried this other method and it has worked without the need of setting XACT_ABORT ON on distributed transactions and works fine without transactions.

exec [MyRemoteServer].MyRemoteBD.dbo.sp_executesql N'
    INSERT INTO SomeTable (col1,col2,...) 
    VALUES (val1,val2,...); 
    SELECT SCOPE_IDENTITY ()'

Upvotes: 1

Oded
Oded

Reputation: 499172

You can create a stored procedure on your linked server that will return the identity.

You should be using SCOPE_IDENTITY() rather than @@IDENTITY, by the way.

See this related question (Best way to get identity of inserted row?).

Upvotes: 5

Related Questions