SCOPE_IDENTITY() from a linked server

Transact-SQL
1

Afternoon all,

I'm inserting a few columns onto a table on a linked server but I seem unable to get the SCOPE_IDENTITY() back. Any idea what i'm doing wrong??

DECLARE @ID INT = 1010
DECLARE @NEW_ID INT

INSERT INTO TestServer2.DavesDB.dbo.Employees(
	Emp_Title, Emp_FName, Emp_LName)
SELECT  
	Emp_Title, Emp_FName, Emp_LName
   	FROM Employees
   	WHERE Emp_ID = @ID
	
SELECT @NEW_ID = SCOPE_IDENTITY()

Many thanks

Dave

2

SCOPE_IDENTITY() does not work across linked servers, at least not with 4 part names via INSERT.

You could rewrite this as a stored procedure that exists on the linked server, and have the procedure provide the new value as a RETURN variable, or as an OUTPUT variable. Example:

:connect TestServer2
USE DavesDB
GO
CREATE PROCDURE dbo.Insert_employee @title varchar(128), @fname varchar(128), @lname varchar(128) AS
SET NOCOUNT ON
DECLARE @id int
INSERT INTO dbo.Employees( Emp_Title, Emp_FName, Emp_LName)
VALUES(@title,@fname,@lname)
SET @id=SCOPE_IDENTITY()
RETURN @id
GO

:connect OtherServer
DECLARE @ID INT = 1010, @NEW_ID INT
DECLARE @title varchar(128), @fname varchar(128), @lname varchar(128) 
SELECT  
	@title=Emp_Title, @fname=Emp_FName, @lname=Emp_LName
   	FROM Employees
   	WHERE Emp_ID = @ID
EXEC @NEW_ID=TestServer2.DavesDB.dbo.Insert_employee  @title=@title, @fname=@fname, @lname=@lname
SELECT @NEW_ID

Note that the :connect directive is only valid if you run this in SQLCMD mode in SSMS. Otherwise you'd connect the query window to the appropriate server name and run each section independently.

If you need to get a non-integer value, an OUTPUT variable would be the only choice:

3

An alternative on the SP idea is to use the remote server's sp_executesql
Something like:

DECLARE @ID int = 1010
	,@NEW_ID int
	,@title varchar(128), @fname varchar(128), @lname varchar(128);

SELECT @title=Emp_Title, @fname=Emp_FName, @lname=Emp_LName
FROM dbo.Employees
WHERE Emp_ID = @ID;

EXEC TestServer2.[master].sys.sp_executesql 
	N'DECLARE @t TABLE (id int NOT NULL);
	INSERT INTO DavesDB.dbo.Employees(Emp_Title, Emp_FName, Emp_LName)
	OUTPUT inserted.emp_id INTO @t
	VALUES(@title, @fname, @lname);
	SET @NEW_ID = (SELECT id FROM @t);'
	,N'@title varchar(128), @fname varchar(128), @lname varchar(128), @NEW_ID int OUTPUT'
	,@title, @fname, @lname , @NEW_ID OUTPUT;

PRINT @New_ID;
4

Awesome,

Thank you very much guys, you given me a different way to to think about it.

Dav