Before I migrated to SQL Server 2012, I was using Visual Studio 2010 Premium, which included Database Projects. It was a great feature that, once I figured out how to use it, enabled me to keep track of all my schema changes automatically, and to deploy them to production in a controlled way that allowed for backing them out again. Unfortunately, it didn’t work for me with SQL Server 2012.

With SQL Server 2012 and SQL Server Data Tools (SSDT), the feature is now available to other Visual Studio users besides those with Visual Studio Premium. There is just one little catch: if you have databases that reference other databases, as I do, the new Database Projects, by default, are apt not to build. When you create a default new database reference within an SSDT database project it won’t work unless you use are using only DB project scripts containing database variable references, not explicit cross-database references contained in your database.

The solution to this problem is very simple, but unfortunately Microsoft didn’t happen to mention it anywhere that you are likely to stumble across it. (It took me the better part of a year to find it!) When you create a database reference, the UI specifies a default DB variable name for you, as if it were doing you a favor. If you don’t remove this name (blanking out the field), any explicit cross-database references contained within the database which you are modeling will be flagged as undefined, and the project will not build.

What you must do, then, is simply clear out the DB variable name field when creating a new DB reference. If you don’t do this and create the reference with the DB variable name then you need to delete that reference and create a new one that does not contain the DB variable name.

I don’t know if this is an issue when migrating from the old DB project format to the SSDT format or not. I was building a new data mart — a major re-write — and I created a fresh database (from a backup) and kept only what I needed from the old one. For that reason, I had no need to migrate the DB project, and I never tried to.