DZone
Cloud Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Cloud Zone > Cross Database Query in Azure SQL Database

Cross Database Query in Azure SQL Database

Query across databases is not a limitation of Azure, but it has to be done with a little finesse.

Grant Fritchey user avatar by
Grant Fritchey
·
Mar. 25, 16 · Cloud Zone · Tutorial
Like (5)
Save
Tweet
5.11K Views

Join the DZone community and get the full member experience.

Join For Free

you can’t query across databases in azure sql database… or can you?

let’s check. i’ve created two new databases on an existing server:

dblist

i’ve created two tables on each respective database:

create table dbo.db1table (
 id int identity(1, 1)
 not null
 primary key,
 val varchar(50)
 );


create table dbo.db2table (
 id int identity(1, 1)
 not null
 primary key,
 val varchar(50)
 );

now, let’s query the db2 table from the db1 database:

select *
from db2.dbo.db2table as dt;

and, here’s the lovely error message:

msg 40515, level 15, state 1, line 35
reference to database and/or server name in ‘db2.dbo.db2table’ is not supported in this version of sql server.

so, like i said, you can’t do three part name cross-database queries in azure sql database… oh wait, that’s not quite what i said is it. let’s do this. let’s create a new security credential within db1 for a login that can get us into db2:

create master key encryption by password = 'securitymatters';

create database scoped credential db2security 
with identity = 'grant',
secret = 'securitymatters';

then, we’ll use that to define an external data source :

create external data source db2access
with (
type=rdbms,
location='myservernotyours.database.secure.windows.net',
database_name='db2',
credential= db2security);

with this, we can put polybase to work and create an external table :

create external table dbo.db2table (
id int,
val varchar(50))
with
(
data_source = db2access);

and, that’s it. if i query dbo.db2table from db1, i get to see data in db2. in short, you can do a cross database query within azure sql database. yeah, it’s going to require some setup and possibly some code modifications since you can’t use the old three-part naming for performing the query, but, you can do it. further, note that these are standard, not premium databases. and, even further, they’re not a part of an elastic pool. it’s just using the external data source and external table to connect the two databases. however, if the one thing keeping you from moving into azure sql database is the ability to query across databases, that’s gone.

Database sql azure

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Top Soft Skills to Identify a Great Software Engineer
  • Why I'm Choosing Pulumi Over Terraform
  • Synchronization Methods for Many-To-Many Associations
  • Open Source Security Risks

Comments

Cloud Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo