SQL Azure - cross-database queries

Question

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.

JuneT · Accepted Answer · 2014-10-05 11:37:38Z

2

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...

answered Oct 5, 2014 at 11:37

JuneT

7,7802 gold badges17 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sirisha Chamarthi · Accepted Answer · 2015-05-21 05:31:29Z

0

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.

answered May 21, 2015 at 5:31

Sirisha Chamarthi

1,29312 silver badges16 bronze badges

Comments

James Thorpe · Accepted Answer · 2016-07-14 12:08:36Z

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.

Collectives™ on Stack Overflow

SQL Azure - cross-database queries

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related