Error SQL72014 on view with cross-database reference while deploying dacpac with Azure pipelines

Jente Stel 0 Reputation points
2023-11-29T09:32:54.84+00:00

I am using the Azure DevOps Pipelines Release task SqlDacpacDeploymentOnMachineGroupV0 to deploy dacpacs to an Azure SQL Managed Instance.

User's image

There are multiple databases in de server that reference each other in stored procedures, which I can successfully implement by adding Database References in the Database Projects that are used to build the dacpacs.

I run into problems when I try to deploy a dacpac with a view that contains a cross-database reference:

CREATE VIEW [dbo].[testview]  
AS 
SELECT Id FROM [$(testdb)].[dbo].[testtable]

The dacpac is built without issue, but the SqlDacpacDeploymentOnMachineGroupV0 task gives me this error:

2023-11-29T08:46:50.1503204Z ##[section]Starting: Deploy using : dacpac trainingdb
2023-11-29T08:46:50.1656712Z ==============================================================================
2023-11-29T08:46:50.1657038Z Task         : SQL Server database deploy
2023-11-29T08:46:50.1657247Z Description  : Deploy a SQL Server database using DACPAC or SQL scripts
2023-11-29T08:46:50.1657624Z Version      : 0.198.0
2023-11-29T08:46:50.1657738Z Author       : Microsoft Corporation
2023-11-29T08:46:50.1657923Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
2023-11-29T08:46:50.1658671Z ==============================================================================
2023-11-29T08:47:27.7183961Z *** Could not deploy package.
2023-11-29T08:47:27.7184756Z Warning SQL72047: The option DisableBroker for service broker is not supported
2023-11-29T08:47:27.7187194Z Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1038, Level 15, State 4, Procedure testview, Line 2 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
2023-11-29T08:47:27.7189387Z Error SQL72045: Script execution error.  The executed script:
2023-11-29T08:47:27.7190884Z CREATE VIEW [dbo].[testview]
2023-11-29T08:47:27.7191934Z AS
2023-11-29T08:47:27.7192599Z SELECT Id
2023-11-29T08:47:27.7194062Z FROM   [$(testdb)].[dbo].[testtable];
2023-11-29T08:47:27.7197164Z 
2023-11-29T08:47:27.7197417Z 
2023-11-29T08:47:27.7197595Z 
2023-11-29T08:47:28.0084584Z ##[error]Publishing to database 'trainingdb_DEV' on server 'XXXXXX.XXXXX.XXXXXX.database.windows.net,3342'. 
Initializing deployment (Start) 
*** The option DisableBroker for service broker is not supported 
Initializing deployment (Complete) 
Analyzing deployment plan (Start) 
Analyzing deployment plan (Complete) 
Updating database (Start) 
Creating View [dbo].[testview]... 
An error occurred while the batch was being executed. 
Updating database (Failed) 
Time elapsed 0:00:26.28 
2023-11-29T08:47:28.0326272Z ##[section]Finishing: Deploy using : dacpac trainingdb

There is no missing alias in the statement, but I've been getting this error everytime I try to deploy a view with a cross-database reference with Azure Pipelines.

Also, if I choose the "Generate Script" publish option, the resulted script can be executed on the database without issue.

Is there anything I can do to resolve this error?

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,293 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Jente Stel 0 Reputation points
    2023-11-29T11:19:47.4066667+00:00

    The Azure Pipelines deploy activity seems not to use the defaultvalue for sqlcmd variables from the project file. The issue was fixed by adding the variable value to the Additional Arguments field:

    User's image

    0 comments No comments