Returning IDENTITY from Linked Server

Question

Post reply

Returning IDENTITY from Linked Server

Gordon Pollokoff

SSCommitted

Points: 1930
More actions
February 7, 2005 at 11:45 am

#66880

I have a procedure that updates tables in a linked server. Is there a way to return the IDENTITY column of a newly inserted row from a linked server.

None of the following seem to work:

INSERT INTO [linkedserver].database.table (........)

  VALUES (.......)

  select @@IDENTITY                 --> returns null

  SELECT SCOPE_IDENTITY()       --> returns null

  select IDENT_CURRENT([linkedserver].database.table)    --> syntax error "The column prefix

'linkedserver.database' does not match with a table name or alias name used in the query

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

olavho SSChasing Mays Points: 646 More actions · Answer 1

Hi,

There definitely seems to be a problem obtaining the identity value through a linked server. Hopefully there are somebody out there who knows a good solution, but if you need a couple of suggestions for workarounds:

1. Use openquery. This will (unfortunately) not necessarily give you the identity of the row you inserted, if another transaction committed shortly after.

select * from

openquery (

LINKEDSERVER,

'select IDENT_CURRENT(''database..table'') as id')

2. The best workaround is probably to write a procedure on the linked server, that handles both the insert, the retrieval of the identity-value, and returning this value back to your procedure.

You can then call the procedure:

exec [LINKEDSERVER].database.owner.proc_name

Good luck!

Joseph Ackerman Old Hand Points: 388 More actions · Answer 2

Joseph Ackerman

Old Hand

Points: 388

February 8, 2005 at 10:05 am

#541167

Can you put the procedure that does the insert on the linked server itself? If so, you could write that procedure to use an output parameter to return identity of the new row.

noeld SSC Guru Points: 96590 More actions · Answer 3

Can you put the procedure that does the insert on the linked server itself?

Sure!

On the remote server:

create procedure proc_name ( @col2 int, @col3 varchar(20), @record_id int output)

begin

set nocount on

declare @err int

insert into tblmane( col2, col3) values (@col2, @col3)

select @record_id = scope_identity(), @err = @@error

return @err

end

If so, you could write that procedure to use an output parameter to return identity of the new row.

On the Local Server:

declare @e_code int, @record_id int,

@col2 int, @col3 varchar(20) --fake data

select @col2 = 1 , @col3 ='DATA'

exec @e_code =[LINKEDSERVER].database.owner.proc_name @col2,@col3,@record_id out

select @e_code as Error_Code, @record_id as Record_Identity

Make sure you granted permissions to the appropriate accounts

HTH

* Noel

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 4

Thanks, all good suggestions.....Unfortunately, for various application architecture reasons I'm stuck with the procedure running on one machine, inserting data into a database on a linked server. Luckly, re-querying the table for the PK was a minor performance hit compare to some of the other issues this proc had

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

PhilPacha Hall of Fame Points: 3645 More actions · Answer 5

PhilPacha

Hall of Fame

Points: 3645

February 8, 2005 at 1:02 pm

#541238

Your statement

select IDENT_CURRENT([linkedserver].database.table)

should be

select IDENT_CURRENT([linkedserver].database.dbo.table)

The owner is a required piece of the four-part name.