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.
The link to the response is no longer valid, please redirect
So for the long delay, but I’ve updated the link to the MSDN blog article.
Link to the script is no longer valid do you have another reference or the script itself that would be helpful
I’ve updated the link to the script on the MSDN blog.
Here is the correct URL:
https://blogs.msdn.microsoft.com/appfabricannounce/2010/07/01/finding-circular-foreign-key-references/