By: Greg Robidoux | Comments (2) | Related: > Monitoring
Problem
One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings. One issue that you may run into is that the stored procedure does not provide data, but an error occurs instead. The error that you receive is "Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails."
Solution
This error is generally due to the database not having a valid owner. The way the sp_helpdb stored procedure was written is that it requires a valid database owner otherwise it returns this error when it can not insert a NULL value in to the temporary table that it creates to gather and display the data.
The exact error that you may see is something like the following:
To get around this issue you need to figure out which databases do not have a valid owner and then update the database with a valid owner. The simplest way to figure out which databases are in question is to run the following command:
SELECT name, suser_sname(sid), convert(nvarchar(11), crdate),dbid, cmptlevel FROM master.dbo.sysdatabases
After you run this command you will get a result set such as the following:
From this list we can see that the ReportServer and ReportServerTempDB databases have a NULL value for the second column which is the owner.
To resolve this issue we need to use the sp_changedbowner stored procedure to give each of these databases a valid owner. The following command will generate T-SQL statements for all of the databases that have an invalid owner. The output creates two commands the first changes the database that you are working in and the second changes the owner to "sa". After you execute this query you need to copy and paste the results into a query window and then run the commands.
SELECT 'USE ' + name + '; EXEC sp_changedbowner ''sa'';' FROM master.dbo.sysdatabases WHERE suser_sname(sid) IS NULL
The above command generates this output for our databases in question.
Copy and paste the above code into a query window.
USE ReportServer; EXEC sp_changedbowner 'sa'; USE ReportServerTempDB; EXEC sp_changedbowner 'sa';
After we run the above commands we get confirmation that the database owners were changed.
At this point if we run sp_helpdb we get the following output instead of the error that was returned when we first tried to run the command.
Simple things like this can make some of the system stored procedures not work properly. This is a pretty simple fix and it allows you to use a pretty useful system stored procedure.
Next Steps
- If you have run into this problem when running sp_helpdb check out the owners of the databases to see if there are any inconsistencies
- Take a look at these helpful system stored procedures
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
