Fixing 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 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".

CodeProject