Fix SSDT error" Could not load type Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption"

The SQL Server Data Tools for SQL Server 2014 (SSDT) are now available for Visual Studio 2012 and 2013.
The SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (SSDT-BI) are also available.
I installed both and after a while the schema compare started failing with the error:

Error 6 Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

The solution that worked for me was to uninstall some components of SSDT and then repair the installation.
The detailed steps are:
1) Open Control Panel and then Programs and Features
2) Uninstall the following items:
    Microsoft SQL Server 2014 Management Objects version 12.0.2299.1
    Microsoft SQL Server 2014 Management Objects (x64) version 12.0.2299.1
    Microsoft SQL Server 2014 Transact-SQL ScriptDom version 12.0.2299.1
3) Select Microsoft SQL Server Data Tools 2013, right click to select Change

press the repair button


Note that this solution fixes the SSDT but may have side effect on the SSDT-BI.

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 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