How to resolve references to system objects in TFS, References to Master/MSDB objects

To continue of TFS/DB Dude subject, I want to share with you
some  issues we had with starting to work with the TFS Database
edition.

This post will discuss "How to resolve references to system objects
in TFS, References to Master/MSDB objects". In next posts
I will describe additional problems, like merge issues, adding
new objects to DB and etc.

Probably you use system objects in your stored procedures,
For example: system tables, system views, DMVs or system
catalogs or you use MSDB objects in your SPs, like sending
emails from DB.

In such cases, when you
run a build of the solution
or database project, you
will get warnings indicating
that the system objects used
in the stored procedures
cannot be resolved.


Here some examples:

XXX has an unresolved reference to object [sys].[schemas].
XXX has an unresolved reference to object [sys].[tables].
XXX has an unresolved reference to object [sys].[dm_db_index_physical_stats].
XXX has an unresolved reference to object [msdb].[dbo].[sp_send_dbmail].

The solution :

To reference a master or msdb database containing system
objects, you can reference to a master.dbschema file or
msdb.dbschema file accordingly to resolve the references
to the system objects.

These files can be found in :

C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\
Extensions\SqlServer\2008\DBSchemas\

The red values can vary, depending of you version of TFS
and DB project.
Note that each supported version of SQL Server has
corresponding master.dbschema and msdb.dbschema files.

You may see problems such as 'master.dbschema does not exist'
when you do Build from other machines, for example,
from TFS Server.
Or when you do a Branch.

Error Example:

C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets (56): File D:\Builds\NeoApp\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\master.dbschema does not exist.

This happens because the path to these files saved in dbproj file
by using relative path, something like this \..\..\xx\xxx\x

One way of dealing with this problem is to make your referenced
dbschema files a part of the solution and reference using
a relative path. For example, create a folder in your
Solution/Project, copy files into it and create the reference to
these files in the folder. This will solve the path issues
between machines.

Finally, here is a link to good article about the
Microsoft Visual Studio Team System 2008 (VSTS) Database Edition