SQL Azure as a linked server -> get identity of inserted row
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.
--create proc on azure database
create proc xxp_GetIdasbegin--exec xxp_GetIdDECLARE @ID INTINSERT INTO dbo.bit_CubeGetParameter DEFAULT VALUES SELECT @ID = SCOPE_IDENTITY();SELECT @IDend-- now run this query on your sql server
exec <"Link Server">.<"Azure Database Name">.dbo.xxp_GetId
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.