VS 2010 Database Project bug?

Question

Hi. I am having a very weird problem with building a SQL Server 2008 Database Project from within Visual Studio 2010. I created the database project and then imported the database objects and settings from a local database that I am working with. I then went to build the database project and got the following error:

SQL03006: View: [dbo].[GovCAStaff] has an unresolved reference to object [CTS_Staff].[dbo].[Client_Assignments].

The problem appears to be that the view GovCAStaff is referencing a table in a different database (CTS_Staff). However, I have numerous functions and stored procedures in the same database project that are referencing tables in a different database but the build process only generates warnings for those, not errors. Other than rewriting the view as a function, does anyone know of a way to get rid of this build error? Is this a known limitation of views within database projects? Anyway, I am really stumped. Have googled this topic and haven't found anything relevant. Any help would be greatly appreciated. Thanks in advance.

Answer 1

The reason this error shows up in views, but not functions and stored procedures, is because that is how SQL Server itself will react if the database/table does not actually exist. In other words, in SQL Server you can define stored procedures and functions that reference tables that don't exist or are otherwise inaccessible. Not so for views.

The way to resolve this issue is to add a 'database reference' (a .dbschema file) to your project so that the project build process knows about the schema of that other database. Where to get this magical .dbschema file?

1. Create another database project (presumably in the same solution) for the other database. This is most convenient, as you can just create a 'project reference' and everything stays up-to-date (you wanted a DB project for that other database, anyway, right?).
2. Create the .dbschema file manually via vsdbcmd.
3. If the database is a 'system' database (e.g. 'master' or 'msdb'), you can use one of the pre-built .dbschema files ({Program Files}\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer{version}\DBSchemas).