Azure cross database queries,External resources, External tables

Question

I am migrating all my VM Sql Server 2012 database to Azure Sql Database. In my current structure I am using cross database queries to fetch data from different database tables.

I have created external table to my parent table using below query

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';  

CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
WITH IDENTITY = 'yourServeradminlogin',  
SECRET = 'yourPassword';  

CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
WITH  
(  
    TYPE=RDBMS,  
    LOCATION='testdbdemoserver.database.windows.net',  
    DATABASE_NAME='myDemoDB2',  
    CREDENTIAL= yourServeradminlogin  
);  
CREATE EXTERNAL TABLE [dbo].[Department](  
    [DeptId] [int] NOT NULL,  
    [Name] [varchar](50) NULL  
)  
WITH  
(  
    DATA_SOURCE = RefmyDemoDB2  
);  


/****** Script for SelectTopNRows command from SSMS  ******/  
SELECT *  
  FROM [dbo].[Employee] E  
  INNER JOIN [dbo].[Department] D  
  ON E.DeptId = D.DeptId

I referred this link https://www.c-sharpcorner.com/article/cross-database-queries-in-azure-sql/

But when I create external table it doesn't shows table in external table folder like shown in below image.

In my case it directly showing in Tables folder.

Anyone knows why I don't see Department table in External Tables folder? How can I add such tables in External Tables folder?

mauridb · Accepted Answer · 2018-05-12 00:10:02Z

1

External tables are available in Azure SQL only to support a feature called "Elastic Queries", that may solve your problem:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

If this is not enough for you, and you really need full cross-database query support, you have to use an Azure SQL Managed Instance:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance

which seems to be exactly what you need.

answered May 12, 2018 at 0:10

mauridb

1,58911 silver badges13 bronze badges

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

4 Comments

Ajay Over a year ago

Thanks for reply. I have Azure Sql. I followed all steps mentions in link but still tables not showing in external tables folder.

Ajay Over a year ago

I have SQL Server Management Studio 2012 locally installed on my machine. I use it to connect to Azure Sql Server. And it shows list of database created on Azure Sql Server

mauridb Over a year ago

SQL Server Management Studio 2012 is not aware of external table existence. You need to update to SQL Server Management Studio 2016 or 2017. Here's the link: learn.microsoft.com/en-us/sql/ssms/…

Ajay Over a year ago

Thanks for reply. Yeah yesterday I have installed 2017 and now it is showing me everything.

Collectives™ on Stack Overflow

Azure cross database queries,External resources, External tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related