Today I’ll show you how to solve one of the most common issues you can face while working with SQL Server Data Tools Database Projects: the circular references.
The Issue
Suppose you have a Visual Studio solution with two database projects, like this one:
where the view vw_reference_to_circular of the SSDT_Main database references the table CircularReferenceTable of the SSDT_CircularReference database in this way:
CREATE VIEW [dbo].[vw_reference_to_circular] AS SELECT * FROM [$(referenced_circular_database_name)].dbo.CircularReferenceTable
Thanks to the SSDT_Main‘s reference to the SSDT_CircularReference database:
the solution builds correctly. This build generates the SSDT_Main and the SSDT_CircularReference DACPACs as output in the bin folder of each project (keep in mind the SSDT_Main DACPAC here generated, because it’ll be used later).
Now, let’s add a view to the SSDT_CircularReference database that references the MainTable of the SSDT_Main database:
CREATE VIEW [dbo].[vw_reference_main] AS SELECT * FROM [$(main_database_name)].[dbo].MainTable AS MT
In order to buid correctly, we are going to add a reference to the SSDT_Main database into the SSDT_CircularReference project in this way:
But after clicking the OK button, we get the following error:
The Wrong Way to Solve It
So how to fix it!? Somewhere in some blog post you can find the hint to reference a pre-builded DACPAC of the SSDT_Main database (the DACPAC previously generated), making sure to select the “Suppress errors caused by unresolved references in the referenced project” option in the Add database reference‘s dialog box:
Checking that option you’ll avoid the error that the SSDT_CircularReference project will rise when looking for the references defined into the SSDT_Main DACPAC.
Similarly, once added the previous reference through the DACPAC, the SSDT_CircularReference project has a new reference, so you have to declare to suppress the reference warnings for the previously created reference to the SSDT_CircularReference database into the SSDT_Main project:
Now the solution builds correctly, but there is an hidden downside. If you try to publish (or debug) the solution in an empty target environment (where no existing database objects already exist), the engine can’t do the work because it faces the circular references issue during the deployment. In fact, if I debug my solution (hitting F5), the engine chooses to deploy the SSDT_CircularReference database first. So it creates the database and the table CircularReferenceTable correctly, but it fails when it creates the vw_reference_main view, because it can’t find the SSDT_Main database in the instance:
...SSDT_CircularReferencebinDebugSSDT_CircularReference.sql(89,1): Error: SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure vw_reference_main, Line 5 Invalid object name 'SSDT_Main.dbo.MainTable'. ... ========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 1 failed, 0 skipped ==========
If the engine had chosen to deploy the SSDT_Main project first, it would encounter the same Invalid object name issue related to the CircularReferenceTable table.
The Right Way to Solve It
Having a SSDT solution that builds but can’t be published without manual operations is useless! So I thought to solve the issue trying to “detach” the part of the SSDT_CircularReference project’s script that causes the circularity. I found the solution thanks to the Composite Projects.
The main concepts behind the refactoring we’re going to do are these ones:
- We need to remove the dependency from SSDT_Main into the SSDT_CircularReference, because I know that the SSDT_Main already depends on SSDT_CircularReference.
- We move the SSDT_Main dependent script from SSDT_CircularReference to the new SSDT_CircularRefDetached.
- We declare that SSDT_CircularReference and SSDT_CircularRefDetached are the same database (if you like .NET, think that they belong to the same namespace).
Removing a script and a reference from SSDT_CircularReference and creating them to the new SSDT_CircularRefDetached is quite simple (you can avoid to set the suppress warnings option for the new reference, because the SSDT_CircularReference will have no references!). We just have to add a database reference to SSDT_CircularRefDetached in order to have a composite project:
Our solution now seems like this one:
If you had tried to add this reference into SSDT_CircularReference pointing to SSDT_CircularRefDetached, you would get the usual circular dependency error, because in that way you were importing the definition of the view vw_reference_main into the SSDT_CircularReference project again.
Now if we try to debug the solution, all works fine, but something strange occurs. Looking at the target instance, the view that references the SSDT_Main is missing:
Checking the output after the deployment operation, it seems the SSDT_CircularRefDetached wasn’t built and deployed:
------ Build started: Project: SSDT_CircularReference, Configuration: Debug Any CPU ------ ... ------ Build started: Project: SSDT_Main, Configuration: Debug Any CPU ------ ... ------ Deploy started: Project: SSDT_CircularReference, Configuration: Debug Any CPU ------ ... ------ Deploy started: Project: SSDT_Main, Configuration: Debug Any CPU ------ ... ========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 2 succeeded, 0 failed, 0 skipped ==========
This happens because the startup project is the only SSDT_Main, so the engine starts to debug the only needed objects to the SSDT_Main (itself and the SSDT_CircularReference). Changing the startup projects to multiple projects, including the SSDT_CircularRefDetached, in this way:
the resulting deployment operation is this one:
------ Build started: Project: SSDT_CircularReference, Configuration: Debug Any CPU ------ ... ------ Build started: Project: SSDT_Main, Configuration: Debug Any CPU ------ ... ------ Build started: Project: SSDT_CircularRefDetached, Configuration: Debug Any CPU ------ ... ------ Deploy started: Project: SSDT_CircularReference, Configuration: Debug Any CPU ------ ... ------ Deploy started: Project: SSDT_Main, Configuration: Debug Any CPU ------ ... ------ Deploy started: Project: SSDT_CircularRefDetached, Configuration: Debug Any CPU ------ ... ========== Build: 3 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 3 succeeded, 0 failed, 0 skipped ==========
But, wait… Now there are too many objects!
It’s clear that the engine has to deploy the SSDT_CircularReference first in order to resolve the SSDT_Main dependency. In fact you can check the projects’ build order right-clicking on the solution and clicking on Project Build Order:
So, once the SSDT_CircularReference and the SSDT_Main databases are deployed, we just need the SSDT_CircularRefDetached will be deployed with the “SSDT_CircularReference” name (the change scripts will be determined appropriately according to the existence of the table CircularReferenceTable in the already deployed SSDT_CircularReference database). So we can just change the target database name in the Debug Settings of the SSDT_CircularRefDetached project in this way:
Now hitting F5 we get the correct deployment:
Obviously if you want to publish your database in a target environment using VS and publishing files, or using the DACPACs, you’ll have to follow the aforementioned build order.
You could rightly think that a Composite Project is not needed to deploy correctly the solution: just remove the reference to the SSDT_CircularReference into the SSDT_CircularRefDetached project, and the solution will build and deploy correctly with the same result. The most important difference is that in this way you will not have a full definition of the resulting SSDT_CircularReference database in a DACPAC. Using the Composite Project is the only way to have a DACPAC (the SSDT_CircularRefDetached one) that contains the definition of all the objects defined in the deployed SSDT_CircularReferenced database.
UPDATE 1 (2015-06-17)
You can download the working Visual Studio 2013 solution here: SSDT_Circular_References.
Remember to update the server name in the Target Connection String of the Properties Debug tab for each project, based on your configuration:
UPDATE 2 (2016-01-17)
I received more than one question about the way to solve a generic case in which n database projects have circular references between them:
The way to solve this case consists to isolate all the shared objects (and the ones referenced by these; eg. a shared view and all the tables/functions used in its definition) in a composite project for each database project. In this way each database will be defined by a couple of database projects: one containing the objects used only inside it (base) and one containing all the objects to be shared with the other databases (shared). Then you have to link the two database project (base + shared) through a “same database” reference (dashed lines in the picture):
In the general case in which also each shared project refers to objects belonging to all the other shared project, another “layer” of shared projects is needed. These new projects contain all the objects to share with all the other shared projects:
In the picture above only the new references (shared <–> shared-shared) are shown for the sake of clarity, but all the other links still exist.
It’s possible to continue to add layers if needed, but in the most cases one layer is enough. For example, if DB 2 Shared is referred by DB 1 Shared and DB i Shared, it’s possible to add the new references directly in the first case, without generating circularity:
Thank you for reading!
Stay tuned for more news on our blog and subscribe to our newsletter if you want to receive our new posts in your mail, get course discounts…
