Get @@identity across servers | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Get @@identity across servers

Hi all, I am inserting into a remote table and need to get the identity value that was last created i.e. insert into [RemoteServer].[RemoteDatabase].dbo.[DB](num)
declare @id int
set @id = @@identity
select @id This is returning NULL for the identity value but works fine if I run it on the server locally. Does anyone know how to pick up the identity value? Thanks,
It would appear that this can’t be done. I am going to use a remote procedure to do the insert and then return the identity value as a parameter.
Instead of @@identity,
select IDENT_CURRENT(‘table_name’)
to retrive the last identity value inserted.
table_name you can replace with complete 4 parts
IDENT_CURRENT and @@IDENTITY do have their pitfalls. Can’t you use SCOPE_IDENTITY() on a remote server? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderator
You can create a procedure with the insert statement on the remote server and execute it from the source server to get the identity value… OR You can try usin sp_exuctesql procedure to execute insert statement on remote computer…. MohammedU.
Microsoft SQL Server MVP
Moderator All postings are provided “AS IS” with no warranties for accuracy.

As MohammedU says, make a stored proc on the remote machine with an output parameter, then in the proc use SCOPE_IDENTITY() to set the output parameter after insert. Safer when things like triggers and multiple users come into play.