SSMS – Generate Scripts – Data only – Cyclic dependencies found error

When trying to generate data only scripts from a Sql Server 2012 database, I was getting this error from Sql Server Management Studio 2012:

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. —> Microsoft.SqlServer.Management.Smo.SmoException: Cyclic dependencies found. at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDictionary(Dictionary2 dictionary) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(List1 objectList, Dictionary2 idDictionary, DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ExecuteQueryUsingTempTable(List1 objectList, List1 list, String query) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveTableOnlyDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.Order(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) — End of inner exception stack trace — at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

 

This database was originally on Sql Server 2008, and I could script it without any problems at that time, which I did many times. Then, about a month ago, we moved it to Sql Server 2012, and when I tried to script the database again, using Sql Server Management 2012, I got this error.

I googled around and found this helpful script to find the offending circular references:

https://azure.microsoft.com/en-us/blog/finding-circular-foreign-key-references/

… which I ran in a query window to see what was wrong. Sure enough, it showed me the circular references and the results were spot on, but I wanted to keep the references in place. Everything worked fine when accessed from Entity Framework, and I was afraid to mess with the relations.   (In fact, the database was created in Sql Server 2012 by the Entity Framework using the update-database command. So, all the relations, foreign keys, indexes, etc came originally from the class definitions in EF.)

Problem Solved!

Then, on a whim, knowing that I had successfully scripted this database before, I connected to the Sql Server 2012 instance using Sql Server Management 2008 R2, and I was able to generated the scripts without error!!

Note, that I did data-only, and I had to allow it to script all database objects. I tried to select only certain tables, but that gave some other errors that I wasn’t willing to track down, so I just did the whole thing.

 

So, good luck to you… I hope this will help the next person who faces this problem.

5 thoughts on “SSMS – Generate Scripts – Data only – Cyclic dependencies found error”

  1. Link to the script is no longer valid do you have another reference or the script itself that would be helpful

Leave a Reply

Your email address will not be published. Required fields are marked *