Handling SMO Errors in SSMS View Dependencies

March 25, 2016

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.

Advertisements

VS – Side-Effects of Upgrading a Project’s .Net Framework Version

September 16, 2013

The other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into SMOscript.

Now, up to the current versions, both applications have been developed using Visual Studio 2008 targeting .Net 2.0. (I prefer to keep requirements to a minimum). But now I thought it was time to migrated them to VS2010/3.5 (client framework), and came across a couple of unresolved mysteries.

Missing Exceptions in SqlCommand.Execute*()

The first mystery is that the behavior of the Execute* methods changed such that ExecuteNonQuery() and ExecuteReader() do not reliably raise exceptions any more if an error occurs in T-SQL.

Take the example

CREATE PROCEDURE TestTemp AS
    CREATE TABLE #T (id int)    
    SELECT * FROM #T    
GO

which checktsql tries to verify (with the SET FMTONLY ON option) like this

BEGIN TRAN
SET FMTONLY ON; EXECUTE [dbo].TestTemp
ROLLBACK

(Note that the transaction is actually created by SqlConnection.BeginTransaction(), but essentially it’s the same mechanism)

SSMS returns an error:

Msg 208, Level 16, State 0, Procedure TestTemp, Line 6
Invalid object name '#T'.

What I noticed after migrating the code to VS2010/.Net3.5 is that some (most?) stored procedures cause the error to be raised as an exception in .Net, and some do not. However, the behavior is consistent for each stored procedure.

Fortunately, I noticed the different behavior only when using the SET FMTONLY ON option, but still, there is a difference depending on which VS or framework version is used.

Teh internets did not really help me – I found a couple of discussions, but no documentation of the change, or how to get the original semantics back:

SMO dependency on System.Core

When I tried to debug (and understand) above issue, and reverted the application back to .Net 2.0, but still build with VS2010, I suddenly got the error message during build:

The primary reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089” which could not be resolved in the currently targeted framework. “.NETFramework,Version=v2.0”. To resolve this problem, either remove the reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

There was nothing I could do to get VS2010 to the original behavior (i.e.: compile successfully) targeting .Net 2.0, even though the same assemblies were referenced both in the VS2008 and the VS2010 project.

This thread on MS connect shows the “amusing” dependency zigzag that SMO assemblies implement, switch dependencies even between minor releases:

Posted by Chris Dennis on 4/20/2011 at 10:19 AM
Installing SQL Server 2008R2 Cumulative Update 7 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1777.0 which again depends on System.Core v3.5.
Posted by Chris Dennis on 3/25/2011 at 12:50 PM
Installing SQL Server 2008R2 Cumulative Update 6 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1765.0 which does not depend on System.Core v3.5. Only version 10.50.1750.9 has a dependency on System.Core v3.5.
Posted by Microsoft on 3/25/2011 at 11:08 AM
Your assertion is correct the Microsoft.SqlServer.Management.SqlParser took a dependency on System.Core v3.5. Any projects which reference this assembly directly or indirectly need to be targeted to .NET Framework V3.5.
This is a nightmare!

T-SQL ‘GO’ and SqlCommand

April 9, 2013

Try to execute a T-SQL script which contains several SQL batches separated by the GO keyword using .Net’s SqlCommand, and you will receive an error message

Incorrect syntax near ‘GO’.

What’s going on here? Why does it work from SSMS or sqlcmd, but not in SqlCommand?

The reason for this behavior is that ‘GO’ is not a statement of T-SQL, but rather

a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

(MSDN). You think you might simply remove all occurrences of GO inside your statement, but that will not always work: Some statements require to be the first statement in a batch

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

So how do you execute a set of SQL statements containing ‘GO’ in C#?

The “lazy” way is to simply split the SQL string at each occurrence of ‘GO’ using string.Split(), but this is really lazy.

Why? Because of the complexity of the language that the T-SQL shells are capable of processing:

  • string literals ” and N”
  • single line comments —
  • multi-line comments /* */
  • nested multi-line comments (!)

The characters ‘GO’ must not be recognized if they occur inside any of these constructs, and they need not be the first characters in a line – the line may also contain spaces or whitespace or comments (just give it a try in SSMS, it’s truly amazing).

The more practical way (mentioned here) to execute an arbitrary SQL string is to use SMO’s Server.ConnectionContext.ExecuteNonQuery().

Or, you write a parser using ANTLR, define the correct grammar to split at the relevant GO keywords, and execute the list of resulting SQL batches separately. 😉


automssqlbackup 0.31 supports MS SQL Server 2012

July 19, 2012

automssqlbackup has been idle for 3 years now, not because I discontinued the script, but simply because it worked! (Thanks everybody for their feedback!)

With the arrival of a new version of SQL Server it was time to adjust the assembly references in the code.

What you will now find is a set of paths to default SQL Server SMO assembly locations, of which you need to activate the one that applies to your system.

automssqlbackup is available for download here.


SMO failing on SQL Azure

March 9, 2012

I was notified that SMOscript fails to generate scripts for SQL Azure databases, see SO here and here.

The reported error message is

Objects in database [DatabaseName] on server [ServerAddress]:

Error:

Index was outside the bounds of the array.

I found reports about SMO access failing on SQL Azure, but I do not have confirmed information whether SP1 of 2008R2 or this Azure SR fix the problem.

If you have information on how to solve this problem, please leave a comment here or answer on SO. Thanks.


Searching for String Literals in T-SQL Code

December 25, 2010

If you have ever worked in a multi-language (i.e. localization-aware) project, you know that you should avoid string literals embedded in code.

To make sure that no untranslatable error messages, warnings or other text that might end up in the user interface is embedded in T-SQL code (MS SQL Server stored procedures, functions, and triggers), you’d need to search each of the code blocks for the string delimiter ‘ (single quote).

I’ll sketch a solution called tsqlfindstrings implemented in PowerShell using SMO to access the code stored in a MS SQL Server database, and .Net regular expressions to find string literals. The script tsqlfindstrings.ps1 needs to be configured similarly to my previous PowerShell solution automssqlbackup.

First, the SQL Server-specific SMO libraries are loaded

$mspath = "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\"
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.ConnectionInfo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.Smo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.SmoExtended.dll")

and connection parameters $dbhost, $dbname, $username, and $password are set.

Next, we define string literals to be excluded from the search results, either complete strings or string beginnings, as string arrays:

$excludedStrings = @(" (", ")", "%", "*", " - ", ", ", "(", "true", "false", " ", """", "=""",
  "uniqueidentifier", "datetime", "int", "decimal", "bit", "float", "Z",  "string", "boolean", "guid")
$excludedBegins = @("'")

The example shows T-SQL keywords as excluded strings and the single quote as first character in a string literal (both used for dynamic SQL, so we want to ignore these).

We connect using the standard SMO objects ServerConnection and Server:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
[void] $srv.Initialize($false) 
[void] $srv.Refresh() 
[void] $srv.SetDefaultInitFields($true)

to find our database $db in $srv.Databases.

In its current version, the script checks these SMO properties for string literals:

  • Tables.Checks.Text
  • Tables.Columns.Default
  • Views.TextBody
  • StoredProcedures.TextBody
  • UserDefinedFunctions.TextBody
  • Triggers.TextBody

If a single quote is found in the property values, the Extract subroutine is called to extract the string literal and match it with the defined excluded strings:

foreach($m in [RegEx]::Matches($s, "'(.+?)'[^']") |
  Where-Object { ($excludedStrings -notcontains $_.Groups[1]) })
{
  $g = $m.Groups[1].Value
  $do = $true
 
  foreach($b in $excludedBegins)
  {
    if ($g.StartsWith( $b ))
    {
      $do = $false
    }
  }

  if ($do)
  {
    Write-Host ($type + " " + $name + ": '" + $g + "'")
  }
}

The first released version of tsqlfindstrings is available for download here.


Strange SMO Errors

December 20, 2010

I came across some strange SMO errors today running my SMOscript utility on a new SQL Server 2008 database (10.0.2531.0):

Error: An exception occurred while executing a Transact-SQL statement or batch.

and

Error: Could not find stored procedure ‘msdb.sys.sp_getProcessorUsage’.

A web search did not yield any results.

It turned out that the code was calling Server.Initialize(false) twice, and removing one of the calls caused the program to run without raising exceptions.

Since the last version of SMOscript is dated May 2010, and I have some files created by SMOscript dated July 2010, some magic event must have caused a different behavior in the SMO libraries. I have no idea.