SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY not working with Linked Server RRS feed

  • Question

  • We have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.

    Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

    Now the problem is the some triggers and stored procedures which need to insert the values generated from SQL 2008 to SQL 2000 databases using functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are not working in this scenario.

      We are fixing this problem by writing another select statement for selecting identity based on unique parameters in that scope.

    We are not aware why this is happening and if there is any solution available.

    Please advice!!!

    Varinder Sandhu http://varindersandhus.blogspot.com/
    • Moved by Tom Phillips Wednesday, January 19, 2011 3:38 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, January 19, 2011 6:32 AM

All replies

  • as per BOL...

    The scope of the @@IDENTITY function is current session on 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.

    same for SCOPE_IDENTITY, IDENT_CURRENT also......


    --------------------------------- Devender Bijania
    Wednesday, January 19, 2011 7:18 AM
  • As alternative

    CREATE FUNCTION dbo.fn_getid() RETURNS int
        SELECT newinvid
               'SET NOCOUNT ON;
                INSERT INTO tempdb..Seq2 DEFAULT VALUES
                SELECT SCOPE_IDENTITY() AS newinvid;') AS O)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 19, 2011 7:30 AM