Unresolved References to Same Database Project

Question

I've created a SQL Server database project in Visual Studio 2013 and imported a DACPAC taken from a production database. When I tried to build the project I get hundreds of SQL71501 and SQL71561 errors (both of which are "unresolved reference to an object"). Examining the scripts the problem is that many views and stored procedures use three part names: [database].[schema].[object].

It appears that Visual Studio 2013 throws this error whenever it comes across a three part name that includes the database represented by the database project. eg If the database project represents database "MyDatabase" and a SQL script in that project includes something like SELECT t.Column1 FROM MyDatabase.dbo.MyTable t then VS 2013 throws either SQL71501 or SQL71561 when I build the project.

Is there any way of suppressing unresolved reference errors that just apply to the current database? I still want Visual Studio to throw errors for unresolved references to external databases.

EDIT, Correction: Originally stated the error code was SQL71501. However, it appears Visual Studio throws both SQL71501 and SQL71561 errors for unresolved references to the same database.

Answer 1

Well actually there are two workarounds. My personal opinion is that there is a bug in SSDT concerning 3-part object names in the current database.

1. Create your project snapshot (dacpac) and reference it as a database reference. Remember to clear database variable field in the 'Add database reference' dialog.
   It works however this approach is not recommended by Microsoft and can cause other problems: https://connect.microsoft.com/SQLServer/feedbackdetail/view/1047094/post-deployment-script-is-not-generated-in-the-publish-script

2. At your code replace all occurrences of MyDatabase.dbo.MyTable with [$(DatabaseName)].dbo.MyTable
   https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1863d960-d32d-4920-9a30-13dc86c6f857/sql71562-unresolved-reference-to-object-followd-by-database-name-in-the-same-project?forum=ssdt&prof=required

Answer 2

In Visual Studio:

1) Open SQL Object Explorer, link your database server, right click on unresolved reference database and "Extract Data tier application"

2) Insert a file path. Normally I use \Documents\SQL Server Management Studio\DAC Packages\ and click OK

3) Waiting for extraction

4) Right click on your project References and Add Database Reference

5) Then replace references in your functions, SP, .. with [$(YourDB)] prefix