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

0
votes

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 )
2
sql-serverdatabasetsqlazureazure-sql-database
I dont think you have to import data. Also it has mentioned that there is no SSIS, SSDT support as of now. it is like creating a reference to an external data source in this case a table lying in different database . – Aravind
@Aravind I ment select - updated – ParPar
I think you might be misunderstanding what the fan-out query is for. It's not intended for database shard A to database shard B. It's more for answering the same query across many shards. EG: what are my sales totals across ALL shards. If you want to cross database query you will want two different database contexts, bring the results together in code, and act from there. – Shannon Lowder

2 Answers

1
votes

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

1
votes

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.