Click here to Skip to main content
Click here to Skip to main content

Fixing SSDT unresolved reference to object error

, 21 Mar 2013
Rate this:
Please Sign up or sign in to vote.
Fixing the SSDT unresolved reference to object error.

The SQL Server Data Tools (SSDT) for Visual Studio 2012 are a great set of tools for database development, but as any tool they have some undesirable "features". One of those "features" is the "unresolved reference to object" error. The "unresolved reference to object" in a stored procedure is a warning, but for the function it is an error. The error fails the build and consequently the the schema compare and update of the database.
The error normally is caused by a query that references an object on other database.

One example of the error is: Error 190 SQL71561: Function: [dbo].[myFunction] has an unresolved reference to object [myDatabase].[dbo].[Product]

In the example the function [myFunction] is using the table [Product] from the database [myDatabase], that isn't the same database being managed by the database project of [myFunction].

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

Look at the "Example usage" text and verify that it looks as expected. Click "OK" to add the reference and that should take care of the 'unresolved reference' errors.

The database reference resolves the schema comparison issue, but trying to build the project produced the following error: Error 408 SQL00208: Invalid object name 'db.schema.table'.

To fix this error, go to the project properties and uncheck "Enable extended Transact-SQL verification for common objects".



License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Rui Inacio
Team Leader
Portugal Portugal
I am an Experienced Senior Microsoft Consultant responsible for developing and defining architectures for various projects in a Microsoft environment, using a Service-Oriented Architecture (SOA) and web based applications.
I am also a project manager using agile methodologies and SCRUM.
Software Quality Assurance is a mandatory in every project I am responsible.
As someone said:
“Anyone can program, but not everyone is a programmer...”
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberSPeacock7817-May-13 0:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 21 Mar 2013
Article Copyright 2013 by Rui Inacio
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid