Are you getting an error with VSTSDB relating to references of system views / objects in your database, like INFORMATION_SCHEMA or sys.columns? I was too – my first thought was to create a reference to the master database or a server project, but that didn’t work. To resolve this error, you need to know about some of the dbschema meta-data files included with the installation of the GDR. I’ll explain the purpose and the appropriate use of these files,including how to get rid of your TSD03006 error, and how these .dbschema files differ from the ones that are created when you build your database (and server)projects.  Please note that the information here relates to VSTS Database Edition GDR R2, and may apply a little differently (if at all) to earlier versions of VSTS Database Edition (VSTSDB).

When you create a new database project and import an existing database, VSTS creates a model of your database, but it doesn’t include everything by default. In order to improve project loading time and the performance of database projects, Microsoft isolated database system views and objects like INFORMATION_SCHEMA, splitting out certain objects and meta-data into separate schema files that can be found (by default) at:

C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2005\DBSchemas

You can replace the 2005 with 2000 or 2008 in the path, depending on the version of SQL Server you are dealing with. The schema files you’ll find in this folder are:

• master.dbschema
• msdb.dbschema
• Microsoft.SqlTypes.dbschema.

master.dbschema

Master.dbschema contains system object types like system views and stored procedures (see the table below for more details).  I was working with a client that had views referencing INFORMATION_SCHEMA.COLUMNS.  For example:

<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">VIEW</span> vErrorLog_Columns

<span style="color: #0000ff">AS</span>

<span style="color: #0000ff">SELECT</span> COLUMN_NAME <span style="color: #0000ff">AS</span> col

<span style="color: #0000ff">FROM</span> AdventureWorks.INFORMATION_SCHEMA.COLUMNS

<span style="color: #0000ff">WHERE</span> TABLE_NAME = <span style="color: #006080">'ErrorLog'</span>

If you create a new database project and import the schema of an existing database that contains a reference to this view, or if you create a new view in your project that contains a similar reference, you will get errors similar to the following:

TSD03006: View: [dbo].[vErrorLog_Columns] has an unresolved reference to object [AdventureWorks].[INFORMATION_SCHEMA].[COLUMNS].

Adding a reference to the master.dbschema file I mentioned above will resolve the references;   However, there are a LOT of objects defined in the master.dbschema file, and so adding a reference to it will likely slow down your project enough to be annoying, so you may want to trim it down considerably.  Before doing so, you’ll want to create a copy of the master.dbschema file, as you’ll want to save the original for future changes.  You can start by deleting everything from within the <model /> element, and then adding back only the elements for objects you are referencing in your database project.   It’s a good idea to include this file with in your source control repository or put it in a central location so that other database developers working on the database can get to it.

Apparently, errors related to tempdb can be resolved in a similar fashion.

Referencing Server Projects vs. master.dbscHema

OK, so what else is in this large master.dbschema file, and how is referencing master.dbschema any different than referencing a server project  or a .dbschema file built from a server project – after all, isn’t a server project composed of objects that are in the system master table?   hrmmm….

As I mentioned earlier, the following table lists the different types of objects within the master.dbschema,file:

It turns out that the master.dbschema file and the .dbschema file built from a server project are mostly unrelated, in that they serve different purposes.  It’s confusing in part because a Server project contains data imported from the master database, and well…  master.dbschema also relates to the system master table and has the word “master” in the file name.

Nothing you do to your server will effect the contents of the master.dbschema file;  It holds static definitions of system objects. These are defined in the master database, and are inherent to the capabilities of the version of the SQL Server instance you have installed; The system objects are not deployed to the resulting server, as it is assumed that they already exist on that target server; they are part of the SQL Server version you are using and if they are different or missing, chances are that you have a corrupt installation.

On the other hand, Server projects consist of user objects added to the master database that can be deployed to the target environment; these include logins, linked servers, etc… So, while the contents of the master.dbschema file will only validate references to system objects in your T-SQL,  referencing server projects will effect your deployment as they can include custom objects.

msdb.dbschema

Contents of the msdb.dbschema include a model of the msdb database, so if your database contains references to objects in the msdb database, which are typically used to store information about SQL Server Agent jobs and replication activities, then you will want to create a reference to this file in the same way.  This file isn’t small, but you should avoid making changes to it; some of the changes I made trying to trim it down led to errors related to missing references. 

Microsoft.SqlTypes.dbschema (2008 only)

The Microsoft.SqlTypes.dbschema is specific to SQL Server 2008 and was added to support its new built-in data types: geometry, geography, and heirarchyId. If your project is not a SQL Server 2008 or does not use these datatypes, you can safely remove the reference.  Though, it is pretty small compared to the other .dbschema files we mentioned, so referencing it shouldn’t effect the performance of your project to any noticeable degree.