Not able to do cross database query in SQL Azure

Question

I have 2 DB on same SQL Azure server and i have same table(TB1) on both DB, now i want to read data from TB1 of DB2 and insert data into TB1 of DB1. I am using below query but getting error.

insert into TB1 select 1,* from [DB2].dbo.TB1

Error Message

Msg 40515, Level 15, State 1, Line 16

Reference to database and/or server name in 'DB2.dbo.TB1' is not supported in this version of SQL Server.

Panfish

cross database queries are not supported in azure sql,try elastic query – Surmount

Use Azure Data Factory to move data from place to place in Azure. – Intact

Answer 1

Yes, you can use the Elastic Query Features on SQL Azure.It's the only way you can perform the cross database Queries.

Here are the detailed Queries to follow:

Run the below Query in your DB1(Since you said like reading the TB1 from DB2 and insert those Data's into your TB2 in your DB1)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STro*ngPaSSe0rD';

CREATE DATABASE SCOPED CREDENTIAL Login
WITH IDENTITY = 'Login',
SECRET = 'STro*ngPaSSe0rD';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='DB2',
    CREDENTIAL= Login
);




CREATE EXTERNAL TABLE [dbo].[TB1]
(
    [Columns] [DataTypes] 
)
WITH (DATA_SOURCE = [RemoteReferenceData])

After these step, you can Query the external table like the Normal table. Though some limitations while using the External table, like you couldn't able to Insert Data's into a EXTERNAL TABLE(Reference table)

Peltast

Trying to do this... on Azure sql, it doesn't seem to like the syntax. many errors, first is "Incorrect syntax near 'IDENTITY'" – Lookthrough

What about under these circumstances? – Toowoomba

If I have the same username/password I can select directly from [DbName].dbo.TableName from within DbName2 on Azure. Maybe they added this ability recently? – Chrysostom

@Chrysostom I'm not sure about this feature. But I'm sure that previously it wasn't possible. Which version of SSMS you are using? – Peltast

17.9.1 worked within the same SQL server today. I'm not sure if it works across sql servers in azure. I think it might, but I've heard from colleagues that it won't work between azure sql and traditional sql server outside of azure. – Chrysostom

Answer 2

Azure supports this cross database query feature since 2015 but needs some extra setup to work and Elastic Query.

The first step is create a security credential:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL DB2Security 
WITH IDENTITY = '<username>',
SECRET = '<password>';

The "username" and "password" should be the username and password used to login into the DB2 database.

Now you can use it to define a external datasource, so DB1 can connect to DB2:

CREATE EXTERNAL DATA SOURCE DB2Access
WITH (
    TYPE=RDBMS,
    LOCATION='myservernotyours.database.secure.windows.net',
    DATABASE_NAME='DB2',
    CREDENTIAL= DB2Security);

Finally, you map the TB1 as a external table from the DB2 database, using the previous external datasource:

CREATE EXTERNAL TABLE dbo.TB1FromDB2(
    ID int,
    Val varchar(50))
WITH
(
    DATA_SOURCE = DB2Access);

You can also accomplish this using the Azure SQL Data Sync, but the data are replicated in one single database and this feature are still a preview version (May/2018) and you always see oldest data (the minimal configurable interval for each synchronization is 5 minutes).

Puritan

do i need to create external table for each table there in DB2 ? – Panfish

do i need to drop the external datasource? – Causeuse

yes need to create external table for each table there in DB2 – Benthamism

Answer 3

You can perform cross database queries using the elastic query features on SQL Azure.

You will have to create an external data source and an external table to be able to query tables on other SQL Azure databases. This article shows how to do it.

Hope this helps.

Sabrinasabsay