sp_helpdb problem (!)

Question

Post reply

sp_helpdb problem (!)

Barkingdog

SSCoach

Points: 18670
More actions
November 26, 2002 at 2:46 pm

#64429

I inherited a sql 2K box. Whenever I run "sp_helpdb" from Query Analyzer on pre-existing databases I get "Cannot insert the value NULL into column ' ', table ' ', column does not allow NULLS. INSERT failed".
But when I created a new "TestDB", sp_helpdb TestDb works as expected! Any idea what's going on and how to fix it?
Bill

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Someone likely messed with the system tables.

I'd detach the databases and then reattach them again. Should repopulate the system tables.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Also, try DBCC CHECKDB with the REPAIR_FAST option if the first run reports errors, it should be able to fix.

If that does not correct can you please post the entire error message including error number?

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

Antares/Steve

I'm having the same problem.When i checked the code of sp_helpdb, the error is generated from the following SQL.

select name, suser_sname(sid), convert(nvarchar(11), crdate),

dbid, cmptlevel from master.dbo.sysdatabases

If i execute the above statement, some of the database gives me Null value for suser_sname(sid).

The reason for the error is that there is no user name associated with the SID in the entry for the sysdatabases table resulting in the above error.

Any suggesstions ???

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

Sorry for confusing everbody.I believe If the dbo is orphaned, it does create this problem. drop the exisiting user and grant him dbo priveleges and it helps.

Barkingdog SSCoach Points: 18670 More actions · Answer 5

Here's what it turned out to be in my case:

for various reasons I had to remove the computer from our NT domain. BUT the dbo for the databases was set to <domain-name>\SQLAdministrator". Adding the computer back into the domain fixed the problem. Guess I better use sp_changeobjectowner to make dbo the owner of all those databases.

Bill

sa24 SSCrazy Eights Points: 8027 More actions · Answer 6

Can someone help me with this.

What i believe is the owner might have been deleted from syslogins table due to some reason. On adding another user, we add him to the db_owner role. Will this be an issue with changes in ownership chain with SP3?

Antares686 SSC Guru Points: 125444 More actions · Answer 7

This really isn't an issue as if you read the documentation MS created this is the reason they tell you to be carefull with your ownership chains. This includes the database itself. The best thing to do is set to a dbo that will never disappear (I prefer SA).