Handling SMO Errors in SSMS View Dependencies

I tried to retrieve the dependencies of a T-SQL function in SQL Management Studio 2008, when SSMS – after some database querying – displayed the following error message:

SSMS: Discover dependencies failed. (Microsoft.SqlServer.SMO)

SSMS: Discover dependencies failed. (Microsoft.SqlServer.SMO)

The message reads

TITLE: Microsoft SQL Server Management Studio
——————————

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (Microsoft SQL Server, Error: 515)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

Clicking on the “Additional Details” button, the SMO exception tree is displayed

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

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)
at Microsoft.SqlServer.Management.SqlManagerUI.ObjectDependencies.DiscoverDependenciesAsync()

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

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)

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

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (.Net SqlClient Data Provider)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

——————————
Server Name: NIIGATA
Error Number: 515
Severity: 16
State: 2
Line Number: 311

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

Off to the internets, I found this Script to fix dependencies in the MSSQLWIKI and gave it a try. It soon pointed to a couple of views and stored procedures that the Dependency Viewer (or the underlying SQL statements) thought contained invalid dependencies, however I opened them and re-compiled them without errors. Only when I extended the script to also include triggers, I found an obsolete dependency and fixed it.

Probably re-compilation fixed internal dependency tracking as the script completed without errors in the end.

What the script actually does is iterate through all T-SQL modules, i.e. stored procedures, functions, view definitions (and, after editing, also triggers), and run sys.sp_refreshsqlmodule on each of them. The script’s magic lies in finding the correct order to process the modules.

Fixing the script

These are my changes to the script:

First, all temporary tables are DROPped, since they stay in the database in case the script (or rather, the call to sp_refreshsqlmodule) causes an error:

if (OBJECT_ID('tempdb..#t_excluded_modules') is not null)
    drop table #t_excluded_modules
    
if (OBJECT_ID('tempdb..#t_modules_refreshed_in_end') is not null)
    drop table #t_modules_refreshed_in_end
    
if (OBJECT_ID('tempdb..#t_user_views_or_tables') is not null)
    drop table #t_user_views_or_tables

if (OBJECT_ID('tempdb..#t_dependency_table') is not null)
    drop table #t_dependency_table

Second, I extended to SELECT statement which retrieves the modules to be checked to also include triggers

        select object_id from sys.objects where
            type in ('V', 'FN', 'IF', 'TF', 'TR')

After a couple of runs, I was able to invoke the View Dependencies dialog again without errors.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: