none
SQL Azure as a linked server -> get identity of inserted row

    Question

  • I've set up our Azure cloud DB to be a linked server to our 'SQL server 2008 R2'-server like this post described: http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx
    I've enabled RPC and RPC Out because I read that somewhere.

    Now the problem is I cannot get the ID of the just inserted record. Please take a look at this test table:
    CREATE TABLE dbo.TEST (
           ID INT IDENTITY(1, 1) NOT NULL
           CONSTRAINT PK_TEST_ID PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)    
    )



    I've also created this stored procedure:

       
    CREATE PROCEDURE test_create @ID INT OUTPUT    AS            
          BEGIN
              -- SET NOCOUNT ON added to prevent extra result sets from
              -- interfering with SELECT statements.
              SET NOCOUNT ON;
              
              INSERT INTO TEST DEFAULT VALUES
              SELECT @ID = SCOPE_IDENTITY()
          END


    I've tried to get the last inserted value through multiple ways but none of them are working:

        DECLARE @ID INT     EXEC AZURE01.TestDB.dbo.test_create @ID OUTPUT     SELECT @ID          INSERT INTO AZURE01.TestDB.dbo.TEST DEFAULT VALUES     SELECT @ID = SCOPE_IDENTITY();     SELECT @ID          INSERT INTO AZURE01.TestDB.dbo.TEST DEFAULT VALUES     SELECT @ID = @@IDENTITY     SELECT @ID          SELECT * FROM OPENQUERY(AZURE01, 'INSERT INTO TestDB.dbo.TEST DEFAULT VALUES; SELECT SCOPE_IDENTITY() AS ID');     DECLARE @ScopeIdentity TABLE (ID int);     INSERT INTO @ScopeIdentity     EXEC AZURE01.master..sp_executesql N'       INSERT TestDB.dbo.TEST DEFAULT VALUES;       SELECT SCOPE_IDENTITY()';     SELECT * FROM @ScopeIdentity;

        INSERT AZURE01.TestDB.dbo.TEST      OUTPUT inserted.ID     INTO @ScopeIdentity     DEFAULT VALUES     SELECT * FROM @ScopeIdentity


    I understand why SCOPE_IDENTITY() and @@IDENTITY don't work (because they are local functions/variables which don't have information from the linked server) but the stored procedure with the output parameter should work, right? (locally on the server it works)

    Anyone? :-)


    • Edited by Zenuka Monday, January 14, 2013 12:04 PM Layout
    Monday, January 14, 2013 8:08 AM

All replies

  • Hello Zenuka,

    You can use the Output-clause to get the inserted identity value.


    Olaf Helper

    Blog Xing

    Monday, January 14, 2013 9:33 AM
  • Hi Olaf,

    I've tried the following:

    INSERT INTO [AZURE01].TestDB.dbo.TEST 
    OUTPUT INSERTED.*
    DEFAULT VALUES

    But I get an error:
    A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

    Did I do something wrong in the linked server setup?




    • Edited by Zenuka Monday, January 14, 2013 12:05 PM Typo
    Monday, January 14, 2013 12:02 PM