14

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

Community
  • 1
  • 1
Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93

4 Answers4

12

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');
guest
  • 25
  • 6
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • Did this behavior change between SQL 2005/2008 to 2008R2? – Gary Kindel Mar 15 '12 at 17:26
  • @GaryKindel: Not sure I understand what exactly you mean, but thanks to your comment I noticed that I missed one thing back then, the `N` at the beginning of the string constant in the first snippet. (Updated my answer now.) It is certainly required in SQL Server 2008 R2, not sure about earlier versions. I wonder if the absence of `N` was the reason behind your asking the question. – Andriy M Mar 15 '12 at 18:14
  • I ran into a problem today caused by trying to use Scope_indentity() on a INSERT via a linkedserver. It just caught me by off guard by this error in my project. I was just wondering if Scope_indentity() was always local in SQL 2008. Thought this might have been new behavior with R2. – Gary Kindel Mar 15 '12 at 18:58
  • @GaryKindel: In 2008 R2 it's the same as in 2008, nothing's changed, i.e. `SCOPE_IDENTITY()` is local to the server (just tested). But why, if you are on SQL Server 2005+, you should be better off using the OUTPUT clause, like this: `INSERT INTO server.db.schema.table (columns) OUTPUT INSERTED.identity_column VALUES (values)`. No dynamic parts, too. – Andriy M Mar 15 '12 at 19:13
  • The only one of these that worked for me was the very first using the remote server's master..sp_executesql – Adam Feb 26 '15 at 14:17
2

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

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

Updated per comments on 2019-04-29.

Roman O
  • 3,172
  • 30
  • 26
  • I had to use an OUTPUT parameter like this: `DECLARE @ScopeIdentity int EXEC [linkedServerName].[database].[schema].sp_executesql N' INSERT INTO [table] ... SELECT SCOPE_IDENTITY()', N'@ScopeIdentityOUT INT OUTPUT', @ScopeIdentityOUT = @ScopeIdentity OUTPUT` – Honza Pačuta Jun 28 '17 at 16:24
0

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.

RacerX
  • 2,566
  • 3
  • 23
  • 21
  • I tried it, but it returns NULL. what's wrong? This is my code: **exec('insert [(Worker)].Identification.dbo.test (name) values (''gg'');select scope_identity()')** – Raymond Morphy Apr 18 '11 at 21:35
  • That wont work. It will return NULL or 0. It will work fine for a local SQL DB, but not for a linked one. – WiredEarp Apr 20 '16 at 04:42
0

thanks for guiding us

reviewing the documentation.

I found the solution.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15#syntax

DECLARE @IntVariable INT,@guardar int  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
DECLARE @ScopeIdentity TABLE (ID int);

/* Build the SQL string one time.*/  
SET @SQLString =  
N'INSERT INTO [DATABSE].DBO.TABLE (ITEM1,ITEM2) VALUES(getdate(),@ID) 
SELECT SCOPE_IDENTITY()';
SET @ParmDefinition = N'@ID tinyint';

/* Execute the string with the first parameter value. */  
SET @IntVariable = 1; 

INSERT INTO @ScopeIdentity
EXECUTE [SERVER_LINKED].master..sp_executesql @SQLString, @ParmDefinition, 
@ID = @IntVariable;  
    
SET @RETURN = (SELECT ID FROM @ScopeIdentity);
print @RETURN
Ulises
  • 11
  • 2