Reference to database and/or server name is not supported in this version of SQL Server

Question

Objective

I'm trying to put data from a couple different Azure databases into a new Azure database with a script similar to the following:

:setvar db1 [dbSource1]
:setvar db2 [dbSource2]
:setvar dbTarget "myNewDatabase"

USE master;
GO

DROP DATABASE IF EXISTS $(dbTarget)
GO

CREATE DATABASE $(dbTarget)
GO

CREATE TABLE $(dbTarget).[dbo].[Tenants](
    [TenantId] [nvarchar](450) NOT NULL,
    [OwnerObjectId] [nvarchar](max) NOT NULL,
    [NetworkId] [nvarchar](450) NOT NULL,
    [EndorserType] [int] NOT NULL,
    [Active] [bit] NOT NULL DEFAULT 1,
    [Region] [nvarchar](450) NOT NULL DEFAULT 'us-central',
 CONSTRAINT [PK_Tenants] PRIMARY KEY CLUSTERED 
(
    [TenantId] ASC
)

CREATE TABLE $(dbTarget).[dbo].[ProductPlan](
    [ProductPlanId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [IsStandard] [bit] NOT NULL,
    [StripePlanId] [nvarchar](max) NULL,
    [IsYearly] [bit] NOT NULL,
 CONSTRAINT [PK_ProductPlan] PRIMARY KEY CLUSTERED 
(
    [ProductPlanId] ASC
)

INSERT INTO $(dbTarget).[dbo].[Tenants] (TenantId, OwnerObjectId, NetworkId, EndorserType) 
    SELECT TenantId, OwnerObjectId, NetworkId, EndorserType FROM $(db1).[domain].[Tenants]

INSERT INTO $(dbTarget).[dbo].[ProductPlan] (ProductPlanId, Name, Description, IsStandard, StripePlanId, IsYearly)
    SELECT ProductPlanId, Name, Description, IsStandard, StripePlanId, IsYearly FROM $(db2).[dbo].[ProductPlan]

Essentially I am creating the tables in the new database with the exact same schemas that are in the source databases and then trying to insert all of data from those source tables into the target tables.

NOTE: This works just fine on local db but fails on the databases in my Azure SQL Server

The Problem

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

So the script created the new database just fine but as soon as it tried to create a table in that database it spat the error above. Trying to switch context by inserting USE myNewDatabase after creating the database and before creating the first table throws another error: USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

Attempted Solutions

I've tried making new connections like the second error states but can't seem to connect to a specific database. I always end up connecting to master and then getting the first error all over again.

Any ideas on what I need to do to make this work? Thank you in advanced!

Answer 1

In Azure SQL database, to create a database, a login must be one of the following:

• The server-level principal login
• The Azure AD administrator for the local Azure SQL Server
• A login that is a member of the dbmanager database role

If you have the permission, you can run create database in each database, like in master DB or user DB.

But Azure SQL database doesn't support USE database or across database operations directly, even these databases are in the same Azure SQL Server.

Azure SQL database only support the across database query with elastic query. That still need many steps to achieve the across query. It's not supported to create table across the database. We must create a new connection(query session) on other databases.

Just for now, it's unsupported and we can not make this work.

For more details, please ref: Transact-SQL syntax not supported in Azure SQL Database