Enterprise Manager shows (no items) under Databases

[Scheck]

Running SQL Server 2000 SP3. This server has about 25 small databases, some of which are offline.

Problem occurs in Enterprise Manager - when expanding the Databases folder, (No Items) is displayed. The databases exist and can be accessed via Query Analyzer.

Also, the Management folder appears to be empty.

Another symptom is when the Security folder is expanded, Logins is selected, and a login is right-clicked to view properties, the following error message is displayed twice:
"Error 220: Arithmetic overflow error for data type smallint, value=32965".

The same error is displayed when you try to create a new database by right-clicking on the Databases folder and selecting "New Database..."

To my knowledge, no changes to the server (patches, SPs) were made when the problem began.

Thanks.

 

[SQLBill]

Have you tried to reinstall just Enterprise Manager? (Select Client Tools Only during the installation, then make sure only Enterprise Manager is selected).

-SQLBill

Posting advice: FAQ481-4875

 

[JFBouchard]

Hi, ensure that your version of enterprise manager match the server (SP3). Or it can be a security issue: I presume you're sysadmin?

Jeff

 

[Scheck]

Thanks for the quick responses.

We haven't reinstalled Enterprise Manager because the problem shows up for all developers who have that server registered in Enterprise Manager on their machines.
Since everyone has the same version as the server (SP3), I assumed that the issue wasn't with the client installations of EM.

I'm mainly a developer and also one of the SQL Server admins - none of the security settings or permissions has changed recently.

 

[SQLBill]

Try this as a test....have one person unregister the instance from Enterprise Manager and then re-register it.

-SQLBill

Posting advice: FAQ481-4875

 

[Scheck]

Several of us tried to re-register, but it had no effect.

 

[mrdenny]

Make sure that the guest acount has access to all the databases. If it won't show up.

Take a look in the sysdatabases and srvid tables. I think you'll find that there are ids at the upper end of the smallint field type. Which will cause the problem.

Someone probable went crazy creating either logins or databases, and you have now hit the upper end of what SQL Server can handle.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Scheck]

Nobody went crazy creating databases or logins (or they just won't admit it).

I ran queries against tables sysxlogins (to check srvid) and sysdatabases.

Table syxlogins had 67 rows, with srvid being NULL in all cases but one, which was 0.

When I ran 'Select * from sysdatabases', I got a few rows returned, along with the same Arithmetic Overflow error as in the original posting, and the highest dbid was 56. Since almost all these rows had a status of 1552, I ran a query excluding this status and I got back the rest of the active databases, with the highest dbid of 60. This time the query ran without error. After running several more queries excluding different status codes, the only status that consistantly returned an error was 1552 (read-only 1024, offline 512, torn page detection 16).

I'm thinking that there must be a connection between that status and the errors, but I just don't know what it is.

 

[mrdenny]

sounds like your sysdatabases table is corrupt. Run dbcc checkdb on it and see what comes up.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)