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:
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
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.
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)
5 Comments. Leave new
Yes… SSMS bug. I have seen the same Exception in my .NET applications.
Thanks!
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