18

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.

Improve this question
5
  • 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. Commented Sep 8, 2014 at 14:39
  • @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. Commented Sep 8, 2014 at 21:10
  • 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. Commented Sep 9, 2014 at 19:10
  • What a fracking pos. Why can't MSFT do this right? Commented May 1, 2015 at 22:17
  • 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? Commented Dec 30, 2020 at 19:11

2 Answers 2

Reset to default
15

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I'm accepting your #2 workaround (using [$(DatabaseName)] as the database name in the scripts) as the answer. I've tried it, it works, and it seems less problematic than using the dacpac as a database reference. Cheers.
It failed to help me trying to deploy a dacpac to azure.. I have my own bugs to solve i cannot waste time on solving microsoft's bugs
I inherited a database project and it does have the workaround for [$(DatabaseName)] and I still get SQL71561 why?
Three years later and the way I've handled this problem has changed. Now I would do a find and replace on the SSDT project, replacing "MyDatabase." (note trailing ".") with "" (empty string). When I did it on my very large SSDT project it only took 15 minutes and achieved two things: 1) The SSDT project would build without errors, allowing me to perform schema compares and other operations; 2) The individual scripts no longer have embedded [$(DatabaseName)] variables which would prevent them from being run as stand-alone SQL scripts (occasionally useful in dev environments).
Don't forget to clean and rebuild
3

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.