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… 🙂
- Measure the Strength of Association Between Two Categorical Variables: Mosaic Plot and Chi-Square Test - September 19, 2018
- How to Better Evaluate the Goodness-of-Fit of Regressions - September 13, 2017
- How to bulk copy Azure ML Experiments from a Workspace to another one or do a Backup of them in Physical Files - February 6, 2017
HI Luca
Thank you for this post
When you say “We declare that SSDT_CircularReference and SSDT_CircularRefDetached are the same database (if you like .NET, think that they belong to the same namespace).” How do you do that ?
How do you create the ref project ?
I created a new DB project and then create a new reference and my project does not build anymore
Something is missing but I do not know what !
THX
Hi Mehdi,
just go to SSDT_CircularRefDetached references and add a database reference to SSDT_CircularReference, choosing the “Same database” option from the Database Location combo. In this way you are declaring you want to virtually import the SSDT_CircularReference objects into the SSDT_CircularRefDetached project.
I’ve just updated the post, adding a link to a working solution to download.
Hello Luca.
Excelent article, thank you very much.
I also have circular reference problem.
I just followed your article, everything works ok until i deploy the SSDT_CircularRefDetached. What it does it creates a full script that will drop the existing tables inside my SSDT_CircularReference, and not just an update validating what exists already.
Can you help ?
Thank you.
Nuno.
Hello Luca.
Thank’s for your reply, but i found what was my problem…. i missed the creation of on reference .. so .. Its working now.
Many thanks for your tutorial.
Best regards,
Nuno.
The the “wrong” solution is dominating the top search results but it didnt feel right.
Thank God I kept digging and thank you for posting this.
Thank you, Abdulla!
Suppose you have 9 db’s with the same circular reference issue. How would detaching solve that issue. All the db’s reference multiple db’s in the same view or store procedure
Sorry Chris, I can’t understand. Could you provide more details of your case, please? Thanks!
I’ve just updated my post. May be the new stuff can help you.
Hi Luca,
thanks for that post. Could you maybe add some information about cascading / combining post Deployment Scripts in composite projects?
Hi Marc,
what you ask can be a good subject for another post 🙂
Thank you
Nice Article, Thanks! How about a Synonym for a table in the same database. I have one database, one project with synonym:
CREATE SYNONYM [dbo].[Table1] FOR [dbo].[Table2]
And all objects using Table1 have unresolved ref errors.
Thanks again!
Hi Josh,
it should work, as you can see here:
https://tinyurl.com/y8aclwb5
Any suggestions on importing a database that references all objects (internal and external) via the three point naming convention: DatabaseName.Schema.SqlObject? Every self referenced SqlObject in the database gets an unresolved reference.
These SSDT projects are all large legacy databases, so changing the scripts to removed the three point naming and/or changing them to SSDT variables would be a major under taking.
Multiple this by 24 self referencing databases, where each database then has a possible 23 external database references makes SSDT hard to sell to management.
I’m currently doing this, and looking at importing the database into SSDT but then running regex replacement functions in powershell to replace the references with the SQLCMD variable (from my blog:- http://wp.me/p65ODR-2e).
Hi Luca,
What if I want to do a schema compare with the SSDT_CircularReference database. Do I need to do it twice and consolidate the results, or is there any way I can combine two projects.
Hi rajaprabu,
unfortunately you have to consolidate the results.
Hi Luca,
do you know if there is a way to execute a part of a Stored Procedure (for example, a “SELECT * FROM [$(DatabaseReference)].Schema.TableName) with a reference to another database within a database project?
Currently I get an error that the object is unknown (because it is in another database on the same server).
Hi Chris,
did you refer to that database using a “Different Database, Same Server” reference?
You can check this blog post: https://www.simple-talk.com/sql/sql-tools/sql-server-data-tools-ssdt-and-database-references/
Sorry to necro an old post, but would it not be possible to keep the SSDT projects a single project per database but with the suppress warning for references on build, and then when coming to deployment to an empty database server to tweak the publish settings so that only tables are deployed on a first pass, then views, then functions and lastly procedures? It seems overly complicated to me to start having to have multiple projects for the same database, especially given that objects may be required to move between those projects if a cross db reference happened to be introduced?
Having followed the above I am having the problem of The Model Already Has an element with the same name.
I have moved a strored procedure from the main project into the ref project (so it can go across databases) I deleted the SP from the main project. I have searched for another copy but not found one other than in the ref project. why am I getting this error and how to fix?