CODEWITHSUNDEEP
sql-serverdatabaset-sqlazureazure-sql-database
ParPar
ParPar

Reputation: 7569

Azure SQL - query an exisiting table from another db using elastic db query

I'm using Azure SQL.

I tried to query across databases (in same server) but then I get this error:

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

then I discovered that this is now possible using Elastic db query, as said in this article:

"elastic database query now supports querying across databases in Azure SQL Database."

I followed the steps in the above article:

  1. CREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLE

My question is about step 4. I didn't understand how exactly I should select data from databaseB.dbo.externalTable. Should I take the column from the create statement of the original table? When I try to do it and then select from it, I get an error:

Error retrieving data from one or more shards. The underlying error message received was: 'One or more errors occurred.'.

What is the easiest, right and practical way to apply it?

UPDATE

This is my sql code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'; 
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred 
WITH IDENTITY = '<usename>', 
SECRET = '<password>';  
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH 
    (TYPE = RDBMS, 
    LOCATION = 'ServerName.database.windows.net', 
    DATABASE_NAME = 'DatabaseName', 
    CREDENTIAL = ElasticDBQueryCred, 
) ;



 CREATE EXTERNAL TABLE ['<schemaName>'].['<tableName>']
( [Name] [varchar](255) NOT NULL,
       [id] [int] NOT NULL
       )
WITH 
( DATA_SOURCE = MyElasticDBQueryDataSrc )

Upvotes: 0

Views: 1766

Answers (2)

ParPar
ParPar

Reputation: 7569

I needed to write a custom name for the external table and add the schema name and table name like this:

 CREATE EXTERNAL TABLE <customTableName>
( [Name] [varchar](255) NOT NULL,
       [id] [int] NOT NULL
       )
WITH 
( DATA_SOURCE = MyElasticDBQueryDataSrc
SCHEMA_NAME = N'<schemaName>', 
OBJECT_NAME = N'<tableName>') 
 )

Then you simply get data from this table:

SELECT * FROM customTableName

Note that you don't have to create all the table fields but only the ones you need.

Upvotes: 2

Torsten Grabs
Torsten Grabs

Reputation: 481

You need to follow the steps described here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-vertical-partitioning/. The piece that seems to be missing in your code is the creation of the external table. Note that you cannot use three- or four-part names in Azure SQL DB. You need to create an external table and then you can refer to the external table like a local table to get remote data.

Hope this helps.

Thanks, Torsten

Upvotes: 1

Related Questions