Using Database Project Cross-Database References

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.

proj_refs

2 comments:

karlian said...

Hi thanks for the blog, it helped on linking up two databases, but could you help me out with a different kind of cross reference.

I need to refer to another database that a third party created and still own/support, but i obviously need to reference the tables in my scripts. I dont want to have to create project/schema file for it, as i dont own it so i cant fix the errors that it raised when i briefly tried it out. So is there a way round this that i can still reference the database, and my scripts wont then have warnings. I am using (.Net 2010/SQL Server 2008)

Thanks for any guidance i am a bit lost at the moment, and spent all day searching the internet to no avail.

Regards
Karyn

Mike K said...

This does not appear to work in VS 2012. The only option similar is that of a DACPAC, which I cannot get created for the two databases I've tried. I was able to create a DBSCHEMA file but VS does not appear to deal with these anymore.