Azure sp_execute_remote / Cross Database query in Sql Azure.

Cross database query is not allowed in Sql Azure. Rather Azure has applied a different logic to use
the same.

Please check my article before proceeding with "sp_execute_remote "

http://an-it-solution.blogspot.in/2017/01/sql-azure-select-from-another-database.html

You need to create a SP in "DbCustomers" with the name of "LogEvents_Insert" which will be called by following query.

The below query will execute in "DbPayments " and insert data into "LogLogin" of "DbCustomers"

EXEC sp_execute_remote @data_source_name  = N'DbUtilityLogging_Datasource',
@stmt = N'LogEvents_Insert @LoginID',
@params = N'@LoginId nvarchar(50)',
@LoginID='narender90'

Share:

Sql azure select from another database / Cross Database connection

Cross database query Sql Azure

Azure cross database query performance

sql azure select from another database

reference to database and/or server name in is not supported in this version of sql server.

All the above statements are reference to the issue we face while shifting data from Sql Server to Azure Database.

While using Sql Server (Any version) we can reference any database with in Sql server according to user rights assigned to user.

We can use statements like "select * from db1.dbo.customers"

But now in Sql Azure the system and terminology has changed.

Now, azure has new concept of External Tables and sp_execute_remote in introduced.

Now we need to know the who is who in Azure.

External Tables = "Is for Select"

sp_execute_remote="Execute External Store Procedures"

Before proceeding.

Please create 2 azure databases as per your choice.

Here I am naming them as.

DbCustomers and DbPayments and keep a reference to them in my examples.

In DbCustomers create table "LogLogin" with following fields.
[Id] [int]  NOT NULL,

[LoginID] [nvarchar](50) NULL

Now the main code of cross reference starts.

In "DbPayments " use the following code
--==================
Note - Marked bold and red are the parameters

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jkl$7k@fylj';

GO

CREATE DATABASE SCOPED CREDENTIAL DbCustomers_Credential WITH IDENTITY = 'UserName', SECRET = 'Password';

GO

CREATE EXTERNAL DATA SOURCE DbCustomers_Datasource

WITH (

      TYPE=RDBMS,

      LOCATION='YourServerName',

      DATABASE_NAME='YourDatabaseName', -- DbCustomers 

      CREDENTIAL = DbCustomers_Credential 

);

GO

CREATE EXTERNAL TABLE [dbo].[LogLogin] (

      [Id] [int]  NOT NULL,

      [LoginID] [nvarchar](50) NULL

) WITH ( DATA_SOURCE = DbCustomers_Datasource)

--==================================================

The above code will create an external table and show the table in "DbPayments" to which you can use simply by writing a SQL query.

Select * from LogLogin or use anywhere is the stored procedures.

Share: