Author – Moulshree S. D., Sr. Data Engineer
On-premises SQL Server does support three- or four-part names, but the Azure SQL Database does not. The Elastic Database query feature enables you to run a Transact-SQL query that spans multiple Databases in Azure SQL Database. The Database migration Assessment throwing error as “Cross-Database queries using three- or four-part names not supported in Azure SQL Database” can be eliminated using Elastic querying on Azure SQL Database.
In this document, we will resolve Cross-Database Queries in Azure SQL Database. An example of an on-premises Cross-Database query is given below:
In Azure SQL Database we will execute Cross Database queries. Pre-requisites are as below:
· Login to Master Database.
· User on the Database from which you need to query from.
· Master Key on the Database on which External Tables will be created.
· Grant Select access on the Database from which you need to query from on the table.
NOTE: Grant can be given by the command given below:
GRANT SELECT ON [SCHEMA_NAME]. [TABLE_NAME]TO USER_NAME;
Execution of Cross Database queries:
Step 1: Create Database Scoped Credential
· The Database Scoped Credential is used to access external location. External location could be your another Database. It is not mapped to any Server login or Database User.
STEP 2: Create External data source
· The External Data Source will be pointed to the Database from which you will be querying the table.
· The External data source TYPE will be RDBMS for Azure SQL Database.
· Location points to the server location.
· Credential contains the Database scoped credential which was created before.
· Database name points to the Database from which the data will be queried from.
STEP 3: Create External Table
· Identity columns are not supported while creating External Table.
STEP 4: Create two-part stored procedure to load data
· The Stored procedure given below inserts into base table test13 and fetches data from the External Tables.
· Meanwhile, External Tables fetch data from another Database using its data source as connection to the other Database.
· This step involves any transformation or statements using data fetched from External Tables.
STEP 5: If source schema is altered.
· If the source table is altered to add a column, then the External Table and Destination table also needs to be altered accordingly.
· If any schema changes are been made, then the External Tables are not altered. However, they could be remade.
· Destination and source tables are base tables therefore they can be altered.
· First, we alter source and add a column as shown below:
· Next, we script External Table as “Create to” and add the column to it as shown below:
· Next, we will delete the External Table and run the SQL query to create External Table.
· We will then alter the base table and add a column on the Database where we need to insert data into and run the stored procedure to load the base table.
This resolves the error Cross Database query using three- or four-part names. Now you can use three or four part names in Elastic query on Azure SQL Database.