Unhandled Exception Attempt to retrieve data

Jul 21, 2011 at 2:51 PM

Unfortunately this latest error does not give a whole lot to go on. There are over 450 views in this particular db so going through each one manually to find this problem is a bit of a pain.  On the bright side it is giving us a chance to clean up some of the unneeded junk in this database.

Unhandled Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException: Attempt to retrieve data for object failed for Server 'MySQLServer'.  ---> System.ArgumentException: Invalid path: There is no collection of UnresolvedEntity objects that is a child of Database.

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetChildCollection(SqlSmoObject parent, String childUrnSuffix, String categorystr, ServerVersion srvVer)
   at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
   at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
   at ExportSQLScript.Program.ScriptDependencyTreeNode(DependencyTreeNode dependencyTreeNode)
   at ExportSQLScript.Program.ScriptDependencyTreeNode(DependencyTreeNode dependencyTreeNode)
   at ExportSQLScript.Program.Run()
   at ExportSQLScript.Program.Main()

Aug 24, 2011 at 6:13 AM

I uploaded a patch to the issue tracker. Please try that and see if it works for you.

Sep 2, 2011 at 3:08 PM

It looks like I have the same exception. Is there an update I can try?

Mar 16, 2012 at 4:28 PM

I suspect this issue is probably resolved by someone by now, but as this seems to be the only reference to this error anywhere on the internet, I will reply anyway and it will hopefully help someone else who has the same problem.

I got the same error when trying to extract scripts for User Defined Functions with dependencies using SMO.

Turns out that one of the User Defined Functions was using the spt_value table in the Sql Server "Master" system database.

We had by chance just moved from Sql 2008 to Sql 2012 and the spt_values table is no longer included in the master database any more, therefore when trying to build our list of scripts, the dependency was no longer available and the "There is no collection of UnresolvedEntity objects that is a child of Database" was thrown.

I guess this could apply to any type of entity that relies on another entity in another database that is no longer available.

What makes it more frustrating is that the exception that comes out of SMO is so damned cryptic and lacking detail, that it takes an age stepping through each script item until the offending script was found!!!

Anyway, I hoppe that helps someone in the future.

Nov 14, 2012 at 9:19 PM

I just tracked down this error in my case. I'm running SQL Server 2008. I have installed  Diagram Support for my database in SQL Server Management Studio. When you install Diagram support, SQL adds a table and some stored procedures. In SQL 2008 the table is a system tabled called dbo.sysdiagrams. In SQL 2005, the table is called dtproperties. Now here is the catch, SQL also installs a system stored procedures called dbo.sp_upgraddiagrams. It looks like the purpose of this stored procedure is to migrate data from SQL 2005's dtproperties table to SQL 2008's sysdiagrams table. It conditionally tests for the existence of the dtproperties table before migrating the data.

So, this stored procedure has a dependency on the dtproperties table which does not exist in my database. ExportSQLScript fails because while walking the dependency tree, it finds the stored procedure depends on dtproperties, but it isn't able to get the dtproperties object. 

For me, all I had to do is

DROP PROCEDURE dbo.sp_upgraddiagrams;

and ExportSQLScript works fine.

Nov 14, 2012 at 10:41 PM

I also posted a patch to http://exportsqlscript.codeplex.com/workitem/14130 which I believe solves this issue.