Adding a Database Reference to an external database

Managing database projects within Visual Studio can be extremely confusing. There seems to be very little information out there on how to use the provided project templates properly, and what little there is is often cryptic and clearly written by insiders. Watch this blog for a forthcoming article which will hopefully clear up some of this confusion, but for now I wanted to share what I’ve learned about referencing other, existing databases from within your Visual Studio database project.

With the 2010 release, Visual Studio’s database project templates appear to want you to encapsulate your entire database within the project – and the IDE. This can create a problem when the new database you’re creating needs to reference some objects from another database on the server (an enterprise data store, for example) – since the project doesn’t know anything about this other database two things happen:

1. You lose those handy Intellisense^TM tips when referencing the external database, and
2. Attempting to build and/or deploy the project spews a bunch of errors about missing references.

An afternoon of hunting revealed that there are a couple of ways to address this – one of which involves creating another project, importing the other database, and then adding a reference to the first project. I don’t know about you, but that sounds like an awful lot of work and overhead for what’s essentially a Reference link.

Fortunately, there is a slightly easier way: The key is the .dbschema file. These files represent the compiled state of a database which is ready to be deployed with Visual Studio or some other tool. But what is useful for us here is that Visual Studio can also use these files to determine what objects are in the database – providing Intellisense^TM and reference mapping. So this is what we need to do:

1. Create a .dbschema file for the external database we want to reference
2. Add that file to your SQL Server 2008 Database Project

Creating the .dbschema file

The reason some people recommend creating another database project in Visual Studio is because that will create the .dbschema file for you, but there is also a handy tool you can use by the name of VsDbCmd. This command-line tool for deploying Visual Studio database projects can be found in the following location:

C:Program FilesMicrosoft Visual Studio 10.0VSTSDBDeployvsdbcmd.exe

(Note: It will be in C:Program Files (x86) on 64-bit Windows.)

While most of the documentation out there focuses on how VsDbCmd can be used to deploy database projects, it can also be used to generate a .dbschema file from an existing database. To do this, use the following syntax:

vsdbcmd /a:Import /cs:”ConnectionString” /dsp:Sql /model:DatabaseName.dbschema

Where ConnectionString is the full connection string for the database you want, and DatabaseName is the name of the file to create.

Adding the Reference

Now that you’ve created the file, it’s time to add it to your Visual Studio project as a Database Reference. Expand the project node in the Solution Explorer, right-click References, then select Add Database Reference…. Finally, make the following changes to the Add Database Reference dialog:

• Select the Database project schema radio button, and Browse to the .dbschema file you just created.
• Check the Define database variable box and enter the name of the external database for the following values:
  • Name:  $(DatabaseName)
  • Value:  DatabaseName
• Check the box that says Update the existing schema object definitions and scripts to use the database reference variables.

After clicking OK, and a bit of processing on Visual Studio’s part, you should now be able to compile your database project without errors.

Please feel free to comment below if you have any questions or information to add.

• Database references in visual studio database project question on the MSDN forums.
• Database References at Home of the Data Dude.
• How to: Import a Schema from a Command Prompt in the MSDN Library.