Unresolved References to Same Database Project

18
votes

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.

2
visual-studio-2013sql-server-data-toolsdatabase-project
I'm wondering about this as well. I found something that seemed to work, but then I got all sorts of build errors. Only success I've had is removing the 3-part naming from all procs, views, and functions for same-database. It's relatively easy to find if you restrict to files of type *.sql and include the schema name, but it seems there should be a better way. - Peter Schott
@PeterSchott: I found a previous Stackoverflow post that seems to confirm removing 3 part names is the only solution: stackoverflow.com/questions/19153996/… . The question includes a link to an old MSDN blog that seems to confirm we can't use 3-part names in SSDT database projects and the only work-around is to remove the database name anywhere it appears. (I also notice the links in the answer point to what I assume is your blog!) That MSDN blog post is 5 years old and I'm disappointed they haven't fixed this issue in subsequent releases. - Simon Tewsi
Well, like I said I had partial success. It looked great until I built and found that a lot of other stuff was now throwing errors. I agree that it's disappointing. - Peter Schott
What a fracking pos. Why can't MSFT do this right? - Dave
I just came across this problem. The OP is over six years old and this is still an issue. What is the right venue to bring this up and (potentially) have it addressed by MS? - Jpirok

2 Answers

13
votes

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

3
votes

In Visual Studio:

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

enter image description here

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

enter image description here

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