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

Retrieving Length of Varbinary Field in NHibernate Linq

January 20, 2016

I wanted to SELECT the length of a field declared as VARBINARY(MAX) and mapped to a C# byte[] property. So naïvely I wrote the query in NHibernate Linq as

session.Query<MyTable>().Where(...).Select(t => new { t.ID, t.Pdf.Length })

Rather unexpectedly, NHibernate decided to translate this Linq query into a sub-select in the form

select t0_.ID as col_0_0_,
  (select cast(count(*) as INT) from myTable t0_) as col_1_0_
from myTable t0_

clearly mistaking the .Length as a COUNT(), rather than a LEN().

RegisterGenerator to the rescue!

Having used NH generators already once or twice, I wrote an extension method

public static int? GetBytesLen(this byte[] array)
{
  return null;
}

a generator class

public class BinaryLengthMethodsHqlGenerator : BaseHqlGeneratorForMethod
{
  public BinaryLengthMethodsHqlGenerator()
  {
    SupportedMethods = new[] {
      ReflectionHelper.GetMethodDefinition((byte[] x) => x.GetBytesLen())
    };
  }
  public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
    ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, 
    IHqlExpressionVisitor visitor)
  {
    return treeBuilder.MethodCall("bytes_length", 
      visitor.Visit(arguments[0]).AsExpression());
  }
}

wired the new method into my registry class

internal class LinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
  public LinqToHqlGeneratorsRegistry()
  {
    RegisterGenerator(
      ReflectionHelper.GetMethodDefinition(
        (byte[] x) => x.GetBytesLen()), 
        new BinaryLengthMethodsHqlGenerator());
  }
}

which is itself registered in NH’s configuration

configuration.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();

and registered the SQL equivalent of the declared pseudo-function “bytes_length” in my NH Dialect

internal class MsSql2008DialectFixed : MsSql2008Dialect
{
  protected override void RegisterFunctions()
  {
    base.RegisterFunctions();
    RegisterFunction("bytes_length", 
      new SQLFunctionTemplate(NHibernateUtil.Int32, "len(?1)"));
  }
}

Next, I modified the above Linq query to

session.Query<MyTable>().Where(...).Select(t => new { t.ID, t.Pdf.GetBytesLen() })

and NHibernate now generates the desired SQL query

select t0_.ID as col_0_0_,
  len(t0_.Pdf) as col_1_0_
from myTable t0_

As I wrote this article, I found that RegisterGenerator() not only supports methods, but also properties. But when I tried and adapted the code to use the byte array’s Length property, RegisterGenerator() threw the exception

Unable to cast object of type ‘System.Linq.Expressions.UnaryExpression’ to type ‘System.Linq.Expressions.MemberExpression’.

and it turned out that .Length is translated into a UnaryExpression of type ArrayLength, rather than a MemberExpression. This phenomenon is covered by at least 2 SO questions, here and here.


Checking Consistency of Database Column Data Types

July 19, 2015

Working on a database project, I noticed that column data types where not specified consistently.

For example, one table might have a column “Name” specified as nvarchar(50), and another one as nvarchar(100). Or a column “Description” specified nvarchar(500) in one table, and nvarchar(max) in others.

To find the inconsistencies, I wrote a script which finds inconsistently typed table columns, and then writes out the column specifications:

with cte as (
  select c.name, t.name tname, c.max_length, count(*) C
  from sys.columns c
  inner join sys.objects o on o.object_id = c.object_id
  inner join sys.types t on c.user_type_id = t.user_type_id
  where o.type = 'U'
  and o.name not in ('sysdiagrams', 'dtproperties')
  group by c.name, t.name, c.max_length
)

The first CTE retrieves all unique combinations of column names and type specifications

, cols as (
  select name from cte
  group by name
  having count(*) > 1
)

The second CTE, which operates on the result of the first CTE, filters out the column names with different type specifications

select c.name, o.name, t.name tname,
  case 
    when t.name = 'nvarchar' and c.max_length = -1 then null
    when t.name = 'nvarchar' then c.max_length / 2
    when t.name = 'varchar' then c.max_length 
    else null
  end max_length
from sys.columns c
inner join cols on c.name = cols.name
inner join sys.objects o on o.object_id = c.object_id
inner join sys.types t on c.user_type_id = t.user_type_id
where o.type = 'U'
and o.name not in ('sysdiagrams', 'dtproperties')
order by c.name, o.name

And finally, we select all table names and column specifications along with their effective data type length.

Note that we need to explicitly exclude the built-in tables “sysdiagrams” and “dtproperties”.


Calling a User Defined TSQL Function using NHibernate Linq

April 26, 2014

NHibernate Linq supports calling UDFs, but the implementation is not straight-forward.

I previously wrote about adding support for .Net string comparison functions in NHibernate Linq. This strategy uses existing .Net methods (such as methods of the System.String class) and maps them onto Linq tree operations which in turn are translated into SQL by the NHibernate database driver.

This time, we want to access a user defined TSQL function which cannot be interpreted as a “method” of a “data type” because it contains generic parameters, such as integers or date values, and is not specific to a single table, such as this example on SO.

Nevertheless my solution is based on that example.

The function I want to access is called dbo.Display_Record and is used to decide whether a record can be displayed in the UI based on a given date range.

CREATE FUNCTION [dbo].[Display_Record]
  (@ValidFrom DATETIME, @ValidUntil DATETIME)
RETURNS INT AS
BEGIN
  RETURN 0 (false) or 1 (true)
END

Note that the return type of the function should not be boolean, due to the interoperation of NH and SQL Server (see bug report here) – use integer values instead.

First, we need a declaration and dummy implementation in C#

    public static class dbo
    {
        public static int DisplayRecord(DateTime? from, DateTime? until)
        {
            return 0;
        }
    }

Next, an Hql (the SQL-like language NH uses internally) generator registry must be registered for the method call “dbo.DisplayRecord”:

    public class DisplayRecordLinqToHqlGeneratorsRegistry
        : DefaultLinqToHqlGeneratorsRegistry
    {
        public DisplayRecordLinqToHqlGeneratorsRegistry()
            : base()
        {
            RegisterGenerator(ReflectionHelper.GetMethodDefinition(
                () => dbo.DisplayRecord(null, null)), 
                new DisplayRecordMethodsHqlGenerator());
        }
    }

And an Hql generator

    public class DisplayRecordMethodsHqlGenerator
        : BaseHqlGeneratorForMethod
    {
        public DisplayRecordMethodsHqlGenerator()
        {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition(
                  () => dbo.DisplayRecord(null, null))
            };
        }

        public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
            ReadOnlyCollection<Expression> arguments, 
            HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            return treeBuilder.MethodCall("dbo.Display_Record",
                visitor.Visit(arguments[0]).AsExpression(),
                visitor.Visit(arguments[1]).AsExpression());
        }
    }

arguments[0] and arguments[1] are the arguments passed to our .Net function, and need to be added to the Linq tree.

Finally, our generator registry needs to be registered using Mapping By Code configuration:

            configuration.LinqToHqlGeneratorsRegistry
                <DisplayRecordLinqToHqlGeneratorsRegistry>();

Now we can write NH Linq queries such as

var records = session.Query<MyTable>
    .Where(record => dbo.DisplayRecord(record.ValidFrom, record.ValidUntil);

and the WHERE condition correctly references our UDF function.


Updating YuJisho: a Unicode CJK Character web dictionary

January 17, 2014

I deployed by first version of YuJisho nearly 4 years ago, and, as I developed more and more MVC applications since then, I felt it was time to migrate the original ASP.Net application to ASP.Net MVC.

ASP.Net allowed (supported?) really messy code, so the challenges for an MVC migration are:

  • Extract business logic from the presentation layer to the business layer
  • Re-write the markup from ASP: controls to use native HTML
  • Re-write postbacks as HttpPost actions (both <form> and Ajax requests)

The layout also got a facelift using basic Bootstrap (version 2) styling, but the UI migration is not yet complete.

The data remains unchanged, containing Unicode 5.2, but an upgrade to Unicode 6.3 and the latest dictionary data is in the pipeline.

Enjoy browsing and searching 😉


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


Shrinking MS SQL Server Tables

September 27, 2013

One of my application logs User Agent identifiers to a database table which, over the years, reached several gigabytes in size.

Trying to reduce the size without losing the information, I created a lookup table for these strings, calculated the foreign keys, and then set the identifiers to NULL.

But, no combination of DBCC SHRINKFILE or DBCC CLEANTABLE would actually cause the .mdf file to reduce its size.

This being the internets, I finally found the solution that worked for me in these posts

I tried

ALTER INDEX Index_name ON Table_Name REORGANIZE WITH (LOB_COMPACTION=ON)

and it worked. The operation needs to be executed on the CLUSTERED index, which is, unless another index created as clustered, the primary key by default. The alternative

ALTER INDEX ALL ON dbo.Mytable REBUILD

may achieve the same effect (I haven’t tried).