Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Problems running sp_helpdb in SQL Server


By:   |   Last Updated: 2007-04-06   |   Comments (2)   |   Related Tips: More > 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:

procedure

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:

adventure works

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.

column name

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.

aliases

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.

adventure works

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


Last Updated: 2007-04-06


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 05, 2013 - 2:31:21 AM - Azeez Back To Top

 

Good Inputs , this is a very helpfull info


Thursday, February 17, 2011 - 6:50:49 PM - Gerardo Filho (Gil) Back To Top

Great post, dude!

Just what I needed! Very helpful!

 

Tks


Learn more about SQL Server tools