Failed to get dependency information - looks like problem is with sql clr aggregate

Topics: Database Publishing Wizard
Dec 26, 2006 at 1:45 PM
Hi.
When scripting my SQL 2005 database the "Gathering of objects to script" encountered an error shown initially as "Exception has been thrown by the target of an invocation".
Going down into the message and to the "Advanced Information" dialog shows that it "Failed to retrieve dependecy information" and then gives the object number and what looks like the dependency that it failed on. The depencency that it failed on is a User Defined Aggregate (sql clr). It's actually from the SQL Server 2005 sample code.

I've queried sys.objects for the object info and included both the initial error text and the advanced information below.

Thanks for what looks like a promisng product.

Andy
Andrew Novick
http://www.Novicksoftware.com


select * from sys.objects where object_id in (1611308950, 1397684127)

name objectid principalid schemaid parentobjectid type typedesc createdate modifydate ismsshipped ispublished isschema_published
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------- ---------------- ---- ------------------------------------------------------------ ----------------------- ----------------------- ------------- ------------ -------------------
Concatenate 1397684127 NULL 1 0 AF AGGREGATE_FUNCTION 2006-11-09 14:05:43.987 2006-11-09 14:05:43.987 0 0 0
uspCIAddressFixPrimaryContact 1611308950 NULL 1 0 P SQLSTORED_PROCEDURE 2006-12-26 09:20:49.900 2006-12-26 09:20:49.900 0 0 0


TITLE: mscorlib
------------------------------

Exception has been thrown by the target of an invocation.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve dependency information (objectid(System.Int32) 1445684298.objecttype(System.Int16) 4.relativeid(System.Int32) 1397684127.relativetype(System.DBNull) .objectname(System.String) uspCIAddressFixPrimaryContact.objectschema(System.String) dbo.relativename(System.DBNull) .relative_schema(System.DBNull) .). (Microsoft.SqlServer.SqlEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=MicrosoftSQLServer&LinkId=20476

------------------------------

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

------------------------------
BUTTONS:

OK
------------------------------

-- asn note the following is from the advanced message:

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
Program Location:

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ReflectionUtilities.InvokeMethod(Object obj, String methodName, Object[] parameters)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.DependencyDiscovery.SubmitDependencyRequest(Server server, IEnumerable`1 urns, Boolean forCreate)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.DependencyDiscovery.GetDependencies(Server server, IEnumerable`1 urns, Boolean forCreate, Boolean orderTablesNeeded)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptSourceFactory.GetCreateDependencies(Server server, IEnumerable`1 sourceUrns, Boolean orderTablesNeeded)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptSourceFactory.CreateScriptSource(Server server, Urn[] urns, DiscoveryTasks discoverDependencies, Boolean orderTablesNeeded)
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingEngine.GetUrnsToScript()
at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingDriver.GenerateScript()
at Microsoft.SqlServer.Management.SqlManagerUI.GenerateScriptMaker.DoScript()
at Microsoft.SqlServer.Management.SqlManagerUI.GenScriptWizForm.Script()

===================================

Failed to retrieve dependency information (objectid(System.Int32) 1445684298.objecttype(System.Int16) 4.relativeid(System.Int32) 1397684127.relativetype(System.DBNull) .objectname(System.String) uspCIAddressFixPrimaryContact.objectschema(System.String) dbo.relativename(System.DBNull) .relative_schema(System.DBNull) .). (Microsoft.SqlServer.SqlEnum)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=MicrosoftSQLServer&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.BuildIDKey(DataRow row, Boolean forParent)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.BuildResult(DataTable dt)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd)
at Microsoft.SqlServer.Management.Smo.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)

===================================

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.BuildIDKey(DataRow row, Boolean forParent)


Dec 26, 2006 at 3:34 PM
Hi Andrew -

Do you know which user-defined aggregate you used? Also, do you happen to have the T-SQL you used to create this database?

Thanks,
alex
Dec 27, 2006 at 10:36 PM
Hi Andrew

Thanks for bringing this to our attention. This is a known issue in the script generation process of a stored procedure which contains a CLR aggregate function. A similar issue with slightly different repro steps is being tracked on https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881.

This issue has been fixed in the upcoming SP2 release of SQL Server 2005. You can download the CTP2 for SQL Server 2005 sp2 from http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en and give it a try.

Thanks
Himanshu
Jan 4, 2007 at 1:24 PM
Hi,
the SP2 CTP fixed the problem. Thanks!

It did seem sort of slow when I ran it. Then I realized that I left the "schema and data" setting on. 2.5 GB of script later it was still chugging away. So I guess it's not that slow considering.

You might want to let the user specify which data to script out. I know I need certain tables but don't want others.

I've done that at another client with a scripting component that's part of db_ghost.

Regards,
Andy

Andrew Novick
http://www.Novicksoftware.com
Jan 4, 2007 at 5:32 PM
Allowing the user to specify which tables to include while scipting data is a feature being considered for our next release. Thanks for the feedback.

Himanshu