SSDT unresolved reference ERROR SQL71561

Question

I've imported a database into a database project and straight from the bat I'm getting 200+ errors about unresolved references. Now i've read several posts and sites like this one (http://blogs.msdn.com/b/bahill/archive/2009/08/26/using-self-referencing-or-local-3-part-names.aspx) that the cause would be the 3 part names.

The solution would be to right-click the database project > refactor > rename server/database references. However my window differs from that of the earlier mentioned website and the drop down menu's are empty. I'm using Visual Studio 2010 Professional with SSDT 2010. Any help on how to go about this is appreciated.

I need to import about 20 to 30 databases and correcting all these errors by hand is pretty much out of the question.

Onder

Answer 1

If these are for the current database, your best bet is to import the project, then just replace "MyDBName.dbo." with "dbo.". If you're trying to reference other databases, you should probably use SQLPackage to extract a dacpac for those, then add database references for them. When adding the database reference, do not choose the option to treat these as a variable unless the names of those databases change from environment to environment. E.g., if you reference MyDB1.dbo.Table1 in your project and "MyDB1" is always the name of that database, you should not try to use a DB Variable.

I've written about this on my blog. Maybe that will give some better examples.

http://schottsql.blogspot.com/2012/10/ssdt-external-database-references.html

and

http://schottsql.blogspot.com/2013/01/ssdt-publishing-and-referenced-databases.html

Teasley

Clearing out the auto-populated Database variable: field when adding a database reference is key. – Sikorski

Answer 2

I thought I had the same issue, but following step 4 cleared it. I first forgot and left in the variable reference! Clearing the field let my project build correctly...

A possible solution is to add a database reference to the database that has the missing object. The reference needs a Data-tier Application (dacpac file) that can be easily generated on the solution with the database project that has the missing object. Press the right mouse button over the database project and selected Snapshot Project. The dacpac file is created on the Snapshots folder. The file should then be copied to a common folder for re-usability.

In the project with the error press the right mouse button over the References and selected Add Database Reference. The Add Database Reference dialog appears: 1. Select the dacpac file 2.Select the database location. The most common option is "Different database, same server" 3.Confirm that the Database name field is as expected 4.Clear the "Database variable" field in the dialog. If this field has a value the queries must use this variable and not the database name

Dorchester

This required the database to be built at least once. Since the database project fails to build, you cannot create the dacpac. Chicken and egg. – Vachon