VS DB Project Deploy to Azure SQL - Level 15, State 1, Procedure Sproc1, Line 16 Reference to database and/or server name in 'database..schema.table' is not supported in this version of SQL Server.

Question

We are attempting to deploy a set of blank databases with schema to Azure SQL.

We have some databases that have sprocs that reference other databases and are included in the db project as database references.

This works to our on premise sql servers but we get an error when trying to publish to Azure sql.

Level 15, State 1, Procedure Sproc1, Line 16 Reference to database and/or server name in 'database..schema.table' is not supported in this version of SQL Server.

How do we solve this or is this possible with Azure SQL? I am aware that we can configure external datasources and external table but in our scenario, all the dbs will be on the same instance of Azure SQL.

The sproc snippet would look something like this.

INSERT INTO [ContentAdmin].[DuplicateFixReferences]
([ReferenceTypeName]
,[ReferenceFieldName]
,[IIdentifiableID]
,[PreviousContentGuid]
,[NewContentGuid]
,[BatchGuid]
,[IsDeleted]
,[CreatedBy]
,[CreatedDate])
SELECT
ReferenceTypeName = 'DSGP',
ReferenceFieldName = 'database.SelfGuided.Program_LOC.VideoResourceGuid',
[LOC].[ProgramLOCID],
PreviousContentGuid = [LOC].[VideoResourceGuid],
NewContentGuid = @OriginalMediaRepositoryID,
@BatchGuid,
0,
@UpdatedBy,
@now
FROM
[$(database)]..schema.table1 [PC]
JOIN
[$(database)]..schema.table2 [PS] ON [PS].ProgramSegmentID = [PC].ProgramSegmentID
JOIN
[$(database)]..SelfGuided.table3 [LOC] ON [LOC].ProgramID = [PS].ProgramID
JOIN
#tmpDuplicateRecords [MEDIA] ON [MEDIA].MediaRepositoryID = [LOC].[VideoResourceGuid]
WHERE
[MEDIA].IsActive = 0

Answer 1

You will have to rethink the design, and move away from the tech debt of this design