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)
- Edited by Zenuka Monday, January 14, 2013 12:04 PM Layout
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