question

LanceLyons-1208 avatar image
0 Votes"
LanceLyons-1208 asked AnuragSharma-08 commented

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.

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


azure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @LanceLyons-1208, just wanted to check on this if you need any more information.

0 Votes 0 ·

Nope thanks. We figured out that this was not a supported approach. These are existing on Prem dbs so we may have to figure out how to go with external sources and external tables if we want to do this or leave the sql on prem.

thanks

1 Vote 1 ·

Rightly said. Thanks.

0 Votes 0 ·

1 Answer

ErikEJ avatar image
0 Votes"
ErikEJ answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.