Getting @@identity in an sp that hits another server...

  • The following insert statement run on server1 returns null for the identity field LOGID.

    INSERT INTO [server2].[mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)

    VALUES

    ('Automated','excel','test',GetDate(),NULL,'Importing...')

    select SCOPE_IDENTITY(), IDENT_CURRENT('ImportedProcessorDataFiles'), @@IDENTITY returns all nulls, but I can get the value if I select max(logid)

    Is there a way to do this without using max of the identity field?

  • You haven't provided a lot of info about your table and your actual queries (and the table names in your two queries don't match), but if I read your situation properly you are trying to run the "select" also on server1.

    Since you are "crossing servers", you are triggering a "hidden session" on server2. The part of your insert that directly affects table ImportLog (or ImportedProcessorDataFiles?) is not really running in your local session or batch on server1, so SCOPE_IDENTITY() and @@IDENTITY can't return any meaningful value.

    Try it this way to see the difference when you force everything into one explicit batch:

    -----------------

    exec [server2].master.dbo.sp_sqlexec

    "INSERT INTO [mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)

    VALUES ('Automated','excel','test',GetDate(),NULL,'Importing...')

    select SCOPE_IDENTITY(), IDENT_CURRENT('[mydb].dbo.ImportLog'), @@IDENTITY"

    -----------------

    In that query I have guessed that ImportLog is the table name containing the logid identity col.

    If you don't mind using IDENT_CURRENT() instead of @@IDENTITY, then you can also do:

    -----------------

    INSERT INTO [mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)

    VALUES ('Automated','excel','test',GetDate(),NULL,'Importing...')

    exec [server2].[master].dbo.sp_sqlexec "select IDENT_CURRENT('[mydb].dbo.ImportLog')"

    -----------------

    Since IDENT_CURRENT() is not dependent on the particular session or batch/sp, etc, then its value is always accessible, once you reach the right object. 🙂

  • Here is a more accurate example:

    On SERVER1 I have database DATABASE1 where there is a table IMPORTLOG with an identity field LOGID.

    On SERVER2 DATABASE2 I have a view V_DATABASE1_IMPORTLOG defined as SELECT LOGID,OTHERFIELDS FROM [SERVER1].[DATABASE1].dbo.IMPORTLOG.

    On SERVER2, DATABASE2 I have code that inserts into the view and tries to retrieve the identity value inserted: 

    DECLARE @LOGID int

    INSERT V_DATABASE1_IMPORTLOG (OTHERFIELDS) VALUES ('OTHERDATA')

    SELECT @LOGID = @@IDENTITY

    Since this doesn't work, I was hoping for a way to do this without having a hardcoded reference to SERVER1 and DATABASE1 in the proc, I wanted to limit the references to the views only. 

    Thanks for your response.

     

Viewing 3 posts - 1 through 2 (of 2 total)

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