Unresolved Reference Errors in Azure Pipelines for Database Project with Views Referencing Tables from Other Databases

Question

I have a database project in Azure Data Studio that references tables from another database within its views. I have added this project to a GitHub repository and am using Azure Pipelines for build and release pipelines. However, during the build pipeline, I am encountering unresolved reference errors due to the views referencing tables from another database. I have read that adding a database reference could help, but I am unsure how to implement this in the pipeline. Can someone provide guidance on how to resolve this issue?

This is the error that I am encountering:

##[error]DB1\dbo\Views\V_Applications.sql(37,6): Error SQL71561: View: [dbo].[V_Applications] has an unresolved reference to object [DB2].[dbo].[Prism_Applications].

Answer 1

Hello Aqsa Kazi,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having unresolved Reference Errors in Azure Pipelines for Database Project with Views Referencing Tables from Other Databases.

Regarding your explanations, this is a common issue when dealing with cross-database references in SQL Server projects, and if you could add a database reference it can help resolve these unresolved reference errors.

So, go to your Azure Data Studio:

• Open your database project in Azure Data Studio.
• Right-click on the project in the Solution Explorer and select Add > Database Reference.
• In the Add Database Reference dialog, choose the type of reference you need. Since you are referencing another database, select System Database or Database depending on your setup.
• Specify the database name and any necessary details.
• Save the changes.

Secondly, make sure that the changes are committed to your GitHub repository. This includes the .sqlproj file and any other relevant files.

Thirdly, it is necessary you modify your Build Pipeline in your Azure Pipelines YAML file, you will need to make sure that the build process includes the database reference. For an example of how you might modify your YAML file:

trigger:
- main
pool:
  vmImage: 'ubuntu-latest'
steps:
- task: UseDotNet@2
  inputs:
    packageType: 'sdk'
    version: '5.x'
    installationPath: $(Agent.ToolsDirectory)/dotnet
- task: DotNetCoreCLI@2
  inputs:
    command: 'restore'
    projects: '**/*.csproj'
- task: DotNetCoreCLI@2
  inputs:
    command: 'build'
    projects: '**/*.csproj'
    arguments: '--configuration $(BuildConfiguration)'
- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'your-azure-subscription'
    ServerName: 'your-server.database.windows.net'
    DatabaseName: 'your-database'
    SqlUsername: '$(sqlUserName)'
    SqlPassword: '$(sqlPassword)'
    DacpacFile: '$(Build.ArtifactStagingDirectory)/your-database.dacpac'
    DeployType: 'DacpacTask'

Lastly, make sure that the user account running the pipeline has the necessary permissions to access both databases.

Success!

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.