Get @@Identity value across servers

[pgraves]

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

 

[tran008]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp

From MS:
The scope of the @@IDENTITY function is the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure, which is executing in the context of the remote or linked server, gather the identity value and return it to the calling connection on the local server.

 

[NoCoolHandle]

First thought.
Create a stored proc on the remote server that returns the identity property as an output parameter.

Second.
Try Scope_Identity()

(I think the first one is what will work.)

e.g.
On Remote Server

Create Proc InsertSomeData
@value1 int,
@value2 varchar(300),
@identity int output
as
insert into SomeTable(col1,Col2)
values (@Value1,@Value2)
Set @Identity=scope_identity()
go

Then from the Local server...

Declare @i int
execute remoteservername.dbname.schema.InsertSomeData 23,'test',@i output
select 'The value is ' + cast(@i as varchar(300))

 

[pgraves]

Thanks tran008.

That is exactly what I thought I would have to do.

Paul

 

[SQLSister]

And never ever under any circumstances use @@identity, it will give you wrong values if you ever put a trigger onteh table that inserts to another table with an identitiy field. Change all your @@identity code to scope_identity() as soon as possible or you will have data integrity issues.

Questions about posting. See faq183-874