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:
- CREATE MASTER KEY
- CREATE DATABASE SCOPED CREDENTIAL
- CREATE EXTERNAL DATA SOURCE
- 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 )
select
- updated – ParPar