6

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)

AstroCB
  • 12,337
  • 20
  • 57
  • 73
Mohamad
  • 1,089
  • 4
  • 19
  • 37

2 Answers2

5

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?).

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • what is your meaning of creating stored procedure? I'm running this linked server code: "insert into [(local)].Identification.dbo.test (name) values ('gg')" now what should I do for getting the id of inserted record? – Raymond Morphy Apr 18 '11 at 20:54
  • @Raymond Morphy - I mean exactly that. Create a stored procedure with that code on the linked server, have it return `SCOPE_IDENTITY()` and you should be OK. Your query only has access to the _local_ server variables, not the remote server variables. – Oded Apr 18 '11 at 20:56
  • Is it mean should I put my insert statement and then SCOPE_IDENTITY() after that in the stored procedure in remote server? – Raymond Morphy Apr 18 '11 at 21:06
  • But I can't add stored procedure to remote SQL server, it is designed before. what can I do? – Raymond Morphy Apr 18 '11 at 21:19
  • @Raymond Morphy - I see. You really should have mentioned that "little" detail. Your only option is doing a select on the same table with all the same values you entered in the WHERE clause. Which may or may not work, depending on the structure and data in the remote database. – Oded Apr 19 '11 at 05:47
1

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 ()'
Rafael Merlin
  • 2,517
  • 1
  • 25
  • 31