February 7, 2005 at 11:45 am
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
February 7, 2005 at 1:39 pm
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!
February 8, 2005 at 10:05 am
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.
February 8, 2005 at 10:54 am
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
February 8, 2005 at 12:35 pm
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
February 8, 2005 at 1:02 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply