CODEWITHSUNDEEP
azure-sql-database
user2243747
user2243747

Reputation: 2957

SQL Azure - cross-database queries

We have two databases let say 'D1' and 'D2'. There is one table in each database, say T1 in D1 and T2 in D2. Now I want to write a store procedure in database D1 which will access table in database D2 something like below code...

USE D1
GO
Create or replace sp_test
(
    --other detials 

    SELECT * FROM  D2.dbo.T2
)
GO

Any idea how can I do this?

Note: Earlier we used to have both the databases on to same server. So I didn't face any problem. But now after migrating to Azure I don't have any idea how we can do this. Also as per my information we don't have linked server feature available in SQL-Azure.

Upvotes: 0

Views: 490

Answers (3)

James Thorpe
James Thorpe

Reputation: 56

This is now possible in SQL Azure. Essentially you need to create an external data source in D1 and an eternal table in D1 that matches the target table in D2.

Something like this

USE D1
-- Not sure if the following line is necessary. Omit it and if SQL Squeals, put it in.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MasterKeyPassword';
CREATE DATABASE SCOPED CREDENTIAL D2User WITH IDENTITY='D2UserID', SECRET='D2UserPassword';
CREATE EXTERNAL DATA SOURCE D2DataSource
WITH
(
    TYPE=RDBMS,
    LOCATION='d2.database.windows.net',
    DATABASE_NAME='D2',
    CREDENTIAL=D2User
);
CREATE EXTERNAL TABLE [dbo].[T2](
    [ID] [INT] NOT NULL,
    [Info] [NVARCHAR] (25) NULL
)
WITH
(
    DATA_SOURCE = D2DataSource
);

Of course you have to replace all the relevant details with your own chosen passwords, database locations etc.

Upvotes: 0

Sirisha Chamarthi
Sirisha Chamarthi

Reputation: 1293

If you are in SQL Azure V12 and your server has a few databases with same edition and you are not permanently depend on cross database transactions(I meant just for a few things, please note that not for cross database queries, this you still need to do at application level), you may want to use sp_bindsession. If you are lucky enough you may end up both databases on the same server. The recommendation is not for any application development but one time things.

Upvotes: 0

JuneT
JuneT

Reputation: 7860

Azure SQL Database (or SQL Azure) don't support cross-database queries, so there's no work around other than getting rid of those cross-database queries...

Upvotes: 2

Related Questions