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? :-)

possible duplicate of [get @@Identity from another server(linked server)](http://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server) — mellamokb, Sep 10 '12 at 14:55
The only problem is that the solution doesn't help me because: "A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement." — Zenuka, Sep 11 '12 at 06:04
And in the comments they suggest using a stored procedure and that also doesn't work. — Zenuka, Sep 11 '12 at 06:16
have you retried this again? they've just announced official support of linked server to Azure SQL Database: http://blogs.msdn.com/b/windowsazure/archive/2012/09/19/announcing-updates-to-windows-azure-sql-database.aspx — JuneT, Sep 20 '12 at 10:37
I've tried it.. Still no luck. I've also asked it on the Windows Azure forms: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/cd418db7-21b3-44dc-a30a-6b74174f164f — Zenuka, Jan 23 '13 at 07:09

score 0 · Answer 1 · answered Jan 02 '13 at 22:34

0

Have you considered using a GUID (uniqueidentifier) field instead of or as well as int?

You can then generate the ID client-side (there's a multitude of tools to generate GUIDs) and pass that straight in your insert.

You then have the choice of re-selecting the row based on the GUID column to get the new int value or just use the GUID field as your PK and be done with it.

answered Jan 02 '13 at 22:34

Joel Mansford

1,306
7
13

Thats a good suggestion but unfortunately not the one I'm looking for... Thanks anyway! – Zenuka Jan 31 '13 at 07:20

score 0 · Answer 2 · edited Mar 07 '13 at 03:18

0

--create proc on azure database

create proc xxp_GetId

as

begin

--exec xxp_GetId

DECLARE @ID INT

INSERT INTO dbo.bit_CubeGetParameter DEFAULT VALUES 

SELECT @ID = SCOPE_IDENTITY();

SELECT @ID

end

-- now run this query on your sql server

exec <"Link Server">.<"Azure Database Name">.dbo.xxp_GetId

edited Mar 07 '13 at 03:18

Ram kiran Pachigolla

20,897
15
57
78

answered Mar 07 '13 at 02:58

Mayur

1

That will indeed select the correct number but I need it to be stored in a variable so I can update local records so after this I need something like: SET @ID = EXEC <"Linked Server">.<"Azure database name">.dbo.xxp_GetId. Any suggestions? – Zenuka Mar 11 '13 at 11:26

score 0 · Answer 3 · answered May 07 '15 at 13:52

0

The issue is the remote server execution.

What you can try is :

EXEC @TSqlBatch AT LinkedServer

What this does is tell the database at the other side to execute the tsql locally. This has many uses. Maybe it can serve in this case as well, as the Scope_Identity() should be executed locally along with the insert.

answered May 07 '15 at 13:52

Amir Pelled

591
4
13

The following query does return the inserted id: `EXEC ('INSERT INTO TestDb.dbo.test DEFAULT VALUES; SELECT SCOPE_IDENTITY()') AT [Azure01]` but I cannot use the results to join or select it into a variable: `OLE DB provider "MSDASQL" for linked server "Azure01" returned message "[Microsoft][SQL Server Native Client 10.0]The partner transaction manager has disabled its support for remote/network transactions. The operation could not be performed because OLE DB provider "MSDASQL" for linked server "Azure01" was unable to begin a distributed transaction.` – Zenuka Jun 16 '15 at 06:46

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

3 Answers3