14

I have a MS DataBase project in Visual Studio -- after doing a Schema Comparison, I am trying to generate a deploy script, the problem is I get hundreds of Unresolved references errors,such as the following two:

Error   385 TSD03006: View: [auditing].[AuditedEntities] has an unresolved reference to object [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].  D:\greatProject\Database\greatProject.Database\Schema Objects\Schemas\auditing\Views\AuditedEntities.view.sql   4   14  greatProject.Database
Error   387 TSD03006: View: [auditing].[AuditedEntities] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[C]::[CONSTRAINT_NAME], [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME] or [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS].[C]::[CONSTRAINT_NAME].  D:\greatProject\Database\greatProject.Database\Schema Objects\Schemas\auditing\Views\AuditedEntities.view.sql   5   69  greatProject.Database

The SPROCS never have issues when they run. Is there a quick and dirty way to fix this? Perhaps even by turning off referential integrity checks perhaps?

Matt
  • 24,866
  • 63
  • 189
  • 295

2 Answers2

26

Your database project is missing a reference to the master database.

Assuming that you are talking about a SQL Server Data Tools (SSDT) database project, you can add the missing reference as follows:

  1. In Solution Explorer, open the context menu on the References node and select Add Database Reference… from it:

    Add database reference via Solution Explorer

  2. In the upper portion of the opening dialog window, select the System database option and choose master from the dropdown list.

    Select the System database <code>master</code>

  3. Finally, press the OK button. The database reference should now be added to your project.

stakx - no longer contributing
  • 80,142
  • 18
  • 159
  • 256
  • 1
    This works. The same answer is given by Microsoft here: https://connect.microsoft.com/SQLServer/feedback/details/787152/ssdt-intellisense-does-not-recognize-system-extended-procedures-like-sp-executesql-or-sp-xml-preparedocument – CJ Dennis Jan 12 '17 at 23:29
  • what if the reference is to another existing database which has its own project outside the solution ? If I add reference as a .dacpac, will the another database updated as well ? – Muflix Apr 18 '17 at 11:50
  • @Muflix: I suggest you post this as a separate question. – stakx - no longer contributing Apr 18 '17 at 12:47
14

I added a reference to the master database to resolve this error, which can be achieved through the following steps.

  1. Right click References under your database project
  2. Click Add Database Reference...
  3. Click the `Database project schema (.dbschema) radio button

  4. Browse to the

    C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\master.dbschema

  5. Click OK

The following question is related. ASP.NET Schema Tables Causing Issues in VSTS

Community
  • 1
  • 1
Muhammad Omar ElShourbagy
  • 6,128
  • 2
  • 31
  • 50
  • 3
    This answer did not work for my environment (VS 2013); the path described did not exist. The answer by stakx worked for me. – Adam Anderson Jun 08 '15 at 14:57