One common issue that SQL Server users may encounter is the “Cannot insert the value NULL into column” error when using the sp_helpdb system stored procedure. This error occurs when the database does not have a valid owner, causing the procedure to fail when trying to insert a NULL value into a temporary table.
To resolve this issue, you can follow these steps:
- Identify the databases without a valid owner by running the following command:
SELECT name, suser_sname(sid), convert(nvarchar(11), crdate), dbid, cmptlevel
FROM master.dbo.sysdatabases
This command will provide a result set showing the databases and their respective owners. Look for databases with a NULL value in the owner column.
- Use the sp_changedbowner stored procedure to assign a valid owner to each database. Generate the necessary T-SQL statements by running the following command:
SELECT 'USE ' + name + '; EXEC sp_changedbowner ''sa'';'
FROM master.dbo.sysdatabases
WHERE suser_sname(sid) IS NULL
This command will generate T-SQL statements for all databases without a valid owner. Copy and paste the generated code into a query window, and then execute the commands.
For example, if the ReportServer and ReportServerTempDB databases have a NULL owner, the generated code would be:
USE ReportServer; EXEC sp_changedbowner 'sa';
USE ReportServerTempDB; EXEC sp_changedbowner 'sa';
- After executing the commands, the database owners should be changed. You can now run the sp_helpdb stored procedure without encountering the error.
By following these simple steps, you can fix the “Cannot insert the value NULL into column” error and ensure that the sp_helpdb system stored procedure works properly. It’s important to address such issues promptly to maintain the functionality of your SQL Server environment.