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.


Surprises Changing the Length of a Character Field in SSMS

December 14, 2013

I needed to convert a couple of CHAR and NCHAR columns to their VARCHAR/NVARCHAR equivalent, and noticed a peculiar behavior of the SSMS (2008 R2) table designer:

For example, when the original column was defined as CHAR(10), and I added the characters VAR at the beginning of the Data Type field and tabbed to the next column, the editor would not keep the resulting VARCHAR(10), but change the data type to VARCHAR(50), as is the default length for VARCHAR and NVARCHAR columns in the designer.

I found that this behavior was discussed in this SO question as occurring in SSMS 2005, confirmed in 2008, and, by personal experience, still exists in 2008R2 and 2012.

It seems that MS either does not care, or that it does not want to fix weird bugs due to “compatibility issues”.


Taking a Database Offline in SQL Server Management Studio

June 2, 2013

To take a database offline in SSMS, simply right-click the database, select Tasks, Take Offline, and you’re done.

Except, if it does not work because there are still open connection to the database.

In this case, you need to find out the connections’ SPID

SELECT SPId FROM master..SysProcesses 
WHERE DBId = DB_ID('MyDatabase') AND SPId <> @@SPID

and kill each of the connections by executing

KILL [spid from query above]

Finally, you can take the database offline from its context menu, or, as you already work in a SSMS Query window, simply type

ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE

I just found this script today, so I’m afraid I cannot give you any sources of this magic wisdom.

I did however find an older post here dealing with SSMS Express Edition.


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. 😉


Restoring a SQL Server Database Backup Created by a Newer Version of SQL Server

March 1, 2013

It is widely known that you can only restore a database backup using the same or a newer version of SQL Server.

There is however a workaround to this restriction. The workaround consists of scripting database and contents and importing the scripts using sqlcmd.

Here’s my scenario:

A database is being developed on SQL 2008 [A], and goes live on SQL 2008 R2 [B]. There is no problem copying the database from one system [A] to the other [B].

Then an error occurs, or something must be debugged or analysed, but developers only have SQL 2008 installed.

A newer version is available [C], but not on the develop machines.

So the SQL Server versions can be summarized as: [A] < [B] <= [C]

And this is the workaround:

  • Create a full database backup on [B]
  • Restore the backup in a new database on [C]
  • In SSMS, locate the new database, and right-click
  • Tasks/Generate Scripts

First, we create the DDL script

  • Save scripts to a specific location
  • Save to file: Single file, Save As: Unicode text

Advanced:

  • Generate Script for Dependent Objects: True
  • Schema qualify object names: True
  • Script Collations: if you need them
  • Script Defaults: True
  • Script CREATE
  • Script for Server Version: select version for [A]
  • Script Logins, Owner, Permissions, etc: as required
  • Types of data to script: Schema only
  • Script Check Constraints, Foreign Keys, Full-Text Indexes, Indexes, Primary Keys, Triggers, Unique Keys: True

Next, Next until DDL file is being generated

Next, we create the Data script. Same steps as above, but

  • Types of data to script: Data only

This may take a while…

In the meantime, we can edit the original DDL file. Open the file (I’ll refer to it a script.sql) in a Unicode-capable text editor.

Since we want to create the database on a dev machine, I tend to skip the DDL statements creating the database:

  • Starting from USE [master] up to the [USER <mydatabase>]. Depending on the contents of the database, you may also want to skip the CREATE USER and CREATE ROLE statements.
  • Eventually, you’ll find the first CREATE (PROCEDURE, TABLE, VIEW, etc.) statement. This is where is gets interesting for us.
  • This CREATE section lasts until you find the first CREATE INDEX or ALTER (TABLE) statement.
  • Copy this section to a new file, as save it (say, script1.sql).
  • In script.sql, select from the first CREATE INDEX or ALTER statement down to the last statement before USE [master]. Save this section as script2.sql.

We end up having these files:

  • script.sql .. the original Schema-only file
  • script1.sql .. the CREATE statements
  • script2.sql .. the indexes and constraints
  • data.sql .. the original Data-only file

The DDL scripts are typically not so large, and can be executed from inside SSMS. The Data file may be more problematic, as even a small database can generate a huge (several GB!) data SQL file.

If SSMS is capable of handling all the SQL files, the correct sequence is:

  • script1.sql .. the CREATE statements
  • data.sql .. the original Data-only file
  • script2.sql .. the indexes and constraints

Why do we split the original Schema-only file? Well, the schema defines constraint, such as foreign keys. Typically, the generation of the data script does not take care of such foreign key constraints. That’s why we apply the constraints only after adding the data.

What to do if the data file is too big for SSMS? We’ll run it from the command line:

Open the command prompt (Win+Run, cmd.exe) and try to connect to your SQL Server [A] using sqlcmd. Type sqlcmd -? to get the list of options.

If you have Windows authentication enabled and SQL Server runs on your local machine, the statement

sqlcmd -d [mydatabase] -Q "SELECT 1"

verifies you can connect to SQL Server. If successful, the expected result will be displayed:

-----------
          1

(1 rows affected)

Use the -i command line switch to declare the data.sql as input file:

sqlcmd [all other switches] -i c:\path\to\data.sql

Again, this command may be busy for quite some time. If you have no idea what is going on, and really really want to know, use this script to watch the inserts from SSMS.


Counting Records of All Tables in SSMS

February 20, 2013

The Amazing FMTONLY Trick

January 15, 2013

I created a stored procedure to generate data for a report which involved several temp tables. To check the intermediate results, I thought I’d output the results of the various temp tables only during development, but skip the temp tables in production:

DECLARE @dev BIT = 0;    -- set to 1 during development
IF @dev = 1 SELECT * from @temp

This works fine in SSMS.

But trying to set the SP as report DataSource in Report Designer (.rdl Editor), VS offered the columns of the first SELECT statement, ignoring the value of @dev.

As you may know, when you define a data source in a VS IDE tool, the IDE invokes the SQL statement using the SET FMTONLY ON setting to retrieve information on the columns of the data source.

One feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the result of the IF condition. This explains why the IF … SELECT always returns the column data of every SELECT statement in the SP.

I searched and found this article on SQLServerCentral on how to output only specific data sets in FMTONLY mode. What I did not know was that you can set FMTONLY anywhere inside a T-SQL procedure!

My variant of the code in the referenced article looks like this:

– Detect whether FMTONLY is set (only then the IF(1=0) will be executed):

DECLARE @fmtonlyON BIT = 0;
IF (1=0) SET @fmtonlyON = 1;
SET FMTONLY OFF;

– Create and SELECT temp tables as you like, IF statements will be handled correctly

– Reset FMTONLY where required

IF @fmtonlyON = 1 SET FMTONLY ON;

A comment on the article also mentions that this method allows #Temp tables in SPs without raising the error message

Msg 208, Level 16, State 0, Line {line}
Invalid object name '#TempTable'.

*Yes, the title of this blog was inspired by this great video. Enjoy 😉