SQL SERVER – Unable to cast object of type ‘System.DBNull’ to type ‘System.String’. (Microsoft.SqlServer.Smo)

When you are driving on a highway, you look forward to the sign boards give you guidance while you are busy driving. These sign boards tell you when your exit is approaching and how you should be dealing with the same. I consider such sign boards inside SQL Server as “Error Messages”. I rely on them so heavily to give me insider information to a possible solution. Not always are these messages super easy to understand. Take for instance the following message. While trying to create a database on my lab server, I got below error related to System.DBNull:

SQL SERVER - Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.Smo) cast-error-01

TITLE: Microsoft SQL Server Management Studio
—————————–
Unable to cast object of type ‘System.DBNull’ to type ‘System.String’. (Microsoft.SqlServer.Smo)
——————————
BUTTONS:
OK
——————————

This was a blind dark tunnel for sure. But, since I always want to know the cause of the error, I captured profiler. Here is the query which was last fired from SSMS.

exec sp_executesql N'SELECT
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'SQLAuthority'

When I executed the query manually in SSMS, I found below

SQL SERVER - Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.Smo) cast-error-02

Since the error message talks about NULL (‘System. DBNull’), I think it is for the collation column above which is returned as NULL. When I checked SSMS, I found that I already had a database with the same name but in offline state.

SQL SERVER - Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.Smo) cast-error-03

Have easy workaround to use T-SQL and perform CREATE DATABASE command to get valid error.

Msg 1801, Level 16, State 3, Line 1
Database ‘SQLAuthority’ already exists. Choose a different database name.

If I bring the database online, then I can see the valid error. It looks like an issue with SQL Server Management Studio. What do you think? Have you ever encountered such error in your environment? I personally hope a better error message pops up. But now you know what to do for this error.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Script level upgrade for database master failed because upgrade step sqlagent100_msdb_upgrade.sql encountered error
Next Post
SQL Server Agent – Unable to start the service – The request failed or the service did not respond in a timely fashion

Related Posts

5 Comments. Leave new

  • Yes… SSMS bug. I have seen the same Exception in my .NET applications.

    Reply
  • Thanks!

    Reply
  • dineshmvpa (@dineshmvpa)
    July 11, 2017 11:41 pm

    i also faced same issue in MS SQL Server 2008. the database name was sys.databases
    Drop database databasename resolved my problem. Your article helped me to identify that.

    Thank you very much

    Reply

Leave a Reply