How to avoid circular references with visual studio 2012 Database project

Question

I am trying to create a database solution in Visual Studio 2012.

I have 5 databases that all exist on the same server, so I have imported each of them into a database project.

Unfortunately there are quite a few objects in the databases that reference objects in other databases, so I have been trying to resolve these by adding a Database Reference to create a Database variable to refer to these cross database instances.

The problem is that I have database A referencing B and also B referencing A, and it won't allow me to add these two references. I get the message box saying "Adding this project as a reference would cause a circular dependency".

Any ideas on approaches to resolving this? I guess one way might be to create a solution for each database, but I would rather not if there is a better way to do it.

We are trying to set up an automated build, so I really need the database projects to compile.

Answer 1

You'll want to extract your database schemas into dacpac files (assuming that you're using SSDT SQLProj files and not the old DBProj files for your projects). You can do this through SSMS or through the SQLPackage command line. Once extracted, put them in some place that all of the projects can hit (and preferably that's still under source control so everyone else can reference it). Add those dacpac files to your projects as database references, probably without the option to use a variable for the DB Name.

I've written up adding database reference on my blog here: http://schottsql.blogspot.com/2012/10/ssdt-external-database-references.html

One additional note - if you're building new databases from these projects, you'll likely need to go through several passes. Turn off the option to run as a transaction and to fail when there's an error so some objects will be created, repeat as needed until all objects are created. I used a variable in my projects called "DeployType" and set up my pre and post deploy scripts to handle a DeployType of "New" differently so it wouldn't attempt to populate/update any data for "New" builds.