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)
values(1)
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,
Paul

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.

Hi,
Instead of @@identity,
execute
select IDENT_CURRENT(‘table_name’)
to retrive the last identity value inserted.
table_name you can replace with complete 4 parts
[RemoteServer].[RemoteDatabase].dbo.[DB]

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 Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

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
SQL-Server-Performance.com 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.

]]>