Managing "unresolved reference to object" errors in SSDT Database Projects

Working with SSDT Database Projects can be a pain when you're working with other databases. After creating a recent multi-database solution with 4 Database Projects in it, I spent about a day trying to get rid of a bunch of errors that said something like "Procedure: [someproc] has an unresolved reference to object [someobject]" (there are also sometimes another set of errors which talk about ambiguity between objects, even when you've clearly properly aliased your objects. What follow are some tips for helping resolve some of these errors, because, as with many error messages with Microsoft data products, they can be super misleading.

All the examples here are going to assume any databases you want to reference live on the same server. You can reference databases on different servers, but that's a bit weirder, and tbh I haven't found a use case for it yet. Just know, that functionality exists, but you'll have to figure that out on your own. This still may serve to help get you started though.

Throughout these examples, I'll give some suggestions about settings to use (e.g. target platforms, database settings, etc.) If you don't know what these settings are, these are meant to be simple suggestions to make your build more stable. In all cases though, if you have need to change them - for example, I suggest using the Simple Recovery model; if you need Full - by all means, do it. I just haven't tested all permutations of these things so I don't know that everything I say will apply equally to all settings.

Database References

If you're going to reference any objects from a different database, and you want your project to build, you'll need a Database Reference. This is a pointer to some place the project can figure out the schema of a given database.

Say I have centralized my logging functionality in a database called "Admin", through a procedure called "Admin.dbo.LogEvent". If I have a project called "Sales" and I need to call that proc. On sales, I make a procedure that includes:

exec Admin.dbo.LogEvent @Msg

If I did that in SSMS, that would work just fine. But Visual Studio doesn't really understand that the place I probably want to deploy all this stuff to lives right along side it. Heck, at the beginning of a project, that other database probably doesn't even exist.

So you add a database reference. You go to the project in the solution explorer, expand References, right click, and select Add Database Reference. 

The MSDN documentation is actually pretty good here, but in a nutshell, you can create a couple different kinds of references. You can reference a:

• dacpac
• This is a self contained file that holds all the schema information for a database (and actually what building a Database Project yields)
• If you have to reference a pre-existing database that is not under your control in your solution, you have to use this option. 
• If that database is created by a different Database Project, you can nab the dacpac from it's bin directory.
• If you don't have access to that, you can extract a dacpac through SSMS by right clicking the database in the explorer, hitting tasks and selected extract data tier application. 
• NOTE, I've had problems with this quite often, but an in depth guide to creating dacpacs is outside the scope of this blog.
• system database (like master)
• You may want to reference something like master in case you have objects which reference stuff like sys.tables, or you have custom marked system objects
• Database project in the current solution.
• If you can control all the databases in a single solution, then you don't need to worry about referencing the location of a dacpac, you can cheat and have the solution basically do this for you.
• If you have the luxury of having all your projects in the same solution, I'd recommend this approach.

Then under Database Location, select Different database, same server. There are two other options there ("Same database", and "Different database, different server") which I'm not going to discuss. The former is for cases where you have a single database managed through multiple Database Projects (e.g. a project per schema) and the latter is for objects on different servers, which as I said before, I'm not covering.)

Give the Database name the literal name of the database.

Now comes an important part. DB projects can use SQLCMD string replacement for database names. Long story short, if you want to use them, you MUST provide a database variable name (e.g. "$(Admin)") and you MUST use it in your other scripts. Right below that section is has a box that says Example Usage which shows how that object must be referenced.

So for our example call listed earlier, to call our procedure we would have our script say:

exec [$(Admin)].dbo.LogEvent @Msg

If you don't need this feature, or don't want to use it, you must leave the Database Variable box empty and then reference the object as you would normally.

This is covered in the tutorials on then Add Database Reference documentation.

Debug Connection String

Project Settings

Next, check the project properties for your DB project. If you can, make sure all the Target Platforms are set the same. I don't know how important this is, but it certainly doesn't hurt to have them be the same. If they're legitimately different target platforms, then leave them as they need to be. But you'll have to fiddle with this yourself.

You can also change properties of the database to build and be deployed to by clicking the Database Settings block on the Project Settings page.

Set up the db however you want. A couple things I'd suggest which caused me a few problems:

• Use SIMPLE recovery model (makes your database auto truncate it's xact log, at the cost of recoverability. You do you though)
• Set database containment to None

Project Settings > Debug

When you build your project and you get all those error messages popping up, that's derived from the debug "Target Connection String". I think it uses this connection string to test the deployment of all your DB Projects. It has a default connection string to a local instance; mine looked like this:

I however use a different local instance so I had to change the connection string by clicking the Edit button and setting up the connection to my local, named instance.

IF YOU CHANGE THIS CONNECTION STRING, MAKE SURE YOU CHANGE THE CONNECTION STRING FOR ALL YOUR OTHER PROJECTS IN THE SAME SOLUTION! Otherwise, when it tries to resolve references, it will get confused as to where to look for the schema of a different project.

Naming Conventions

Objects in Same Database

SSDT has a hissy fit if you don't use the right object naming conventions. If I'm operating in a database called "Sales", and I want to select from a table called "Sales.dbo.Sales", I MAY NOT use three-part identification (e.g. Sales.dbo.Sales). I MUST use two part identification (e.g. dbo.Sales). Otherwise it freaks out. Not a bad idea considering you can dynamically change the name of the local database, which woudl cause your code to be invalid in those circumstances, but it trips me up all the time. So first things first, make sure any references to objects on the database your project pertains to are two-part identified.

Object in Referenced Databases

For objects in other databases, you obviously have to use three-part identification (e.g. Admin.dbo.LogEvent). The only gotcha here is how you set up your database reference. If you gave it a variable name (e.g. $(Admin)), you must reference your objects that way (this is also how the Example Usage box shows when you set up your database reference.) See that section, or the documentation on database references for more detail

Capitalization

Depending on how your database, reference, or project are set up, you may get yelled at for improper casing of objects. E.g. if you have a schema called "Rdf" and you have a proc reference an object called "rdf.MyStuff", it will say something like "object differs just by casing". Usually these are yellow warnings, not red, build-preventing errors, but it's something to be aware of. If you run into problems, I'd suggest making your database project settings uncheck "Validate casing on identifiers" and changing the "Database Settings" to use a case-insensitive collation.

Objects referencing objects referencing objects...

One of the biggest causes of reference errors is made obscure by the fact that you might be getting an error for one object when the actual problem lies in one of the objects that object references.

For instance, you may have an object local to your database project that you reference with a two-part identifier, which selects from a view (also referenced correctly) and within that view, there is a three-part identified table in the same database. That can bubble up and cause the top level object to throw errors.

Long story short, you basically have to make sure all your objects are ok all the way down before higher level errors will go away. Kind of annoying, but once you know stuff about naming conventions, you can double click through the list of errors and fix everything you find. You'll probably still have some errors left, but then at least you know you've cleaned up all errors of a certain type.

Object Existence

If your project connection string is targeting a database where a referenced object is missing, you'll get errors that are difficult to track down. Make sure that database has all the referenced objects.

Aliasing

I don't know that this actually matters, but some articles I read seemed to suggest aliasing tables or functions like "myTable a" was less desirable than "myTable as a". I don't really think it matters that much, but if you get stuck on something, maybe give it a shot.

Rebuilding and Troubleshooting

The process of troubleshooting usually looks something like this:

1. Try to build the project/solution
2. Lots and lots of errors/warnings pop up
3. Double click through each error in turn fixing things like bad naming conventions, making sure and target objects exist, making sure all objects referenced by your referenced object exist and are name-formatted correctly
4. Rebuild the project/solution (sometimes the errors don't go away until you've tried to rebuild).
5. Repeat until the errors have disappeared.

Conclusion

This is sort of a pain in the butt, but hopefully gives you some suggestions of things to look at when you see this overwhelming list of vague errors in Database Projects.