Returning IDENTITY from Linked Server

  • 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

  • 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!

  • 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.

  • 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

  • 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

  • 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