Visual studio an excellent project template for database management; currently it supports project templates for 2000, 2005, and 2008 SQL Server. Depending on the version of Visual Studio you are using, you may need to install the latest for 2008 templates here. In this post I will describe a scenario where you might be using linkservers to reference a database that you possibly aren’t managing or do not want to add to the database solution you are working on. Below describes the scenario I came up against and used the cross-database reference to solve it.
Scenario: You have a database solution with various database projects. In one of these database projects, you have some code, say in a stored procedure, that has a line “SELECT … FROM MyRemoteServer.MyRemoteDb.dbo.TableAbc A …”. When you go to build your database project, it fails and says “… has an unresolved reference to object …”. So, we needed a way to have a reference to this report database schema without creating a project within our solution.
Fix: A way to fix this scenario is to use a cross-database references. This msdn article outlines some of the reasons you might want to do this http://msdn.microsoft.com/en-us/library/bb386242%28VS.80%29.aspx. In our case we didn’t want to create a project for this remote database schema that didn’t change. So in order to fix the references that cause the build errors we had to import the remote database into a .dbschema file using the VSDBCMD tool. This can be found here http://msdn.microsoft.com/en-us/library/dd172135.aspx. Once the .dbschema file is create you can add it to you solution. Once it’s in the solution, you can go to your project, right click References and do a “Add Database Reference…”. Choose “Database project schema” for which you will browse to the .dbschema file that was just added to solution. Once you have added the reference, go to the project properties and view the references (see picture below). You will need to associate a server and database variable name for this reference. Now that you have these variables the problematic build code described in the Scenario will need to be changed to this “SELECT … FROM [$(ServerVarName)].[$(DatabaseVarName)].dbo.TableAbc A …”. With this in place the build will succeed because the project can now resolve the remote database objects through the addition of the .dbschema file.