Enterprise Manager Databases folder shows "No items"

We're running a Win2k Adv Server MS Cluster using two servers with SQL
instances called SQL1 and SQL2 running on them. For some reason, we can't
manage SQL2 via Enterprise Manager recently since the databases folder is now
empty. The databases are there and I can see them via Query Analyzer, but I
just can't manage them via EM. "Management" folder is also empty, but if I
refresh (over and over) I sometimes can access the "SQL Server Agent" items.
Only changes that were made to the servers was to install some security
patches, but none that related to SQL server. Other SQL servers with the
same patches work fine. SQL1 instance can be managed with EM just fine.
I suspect something is wrong with SQL2 instance, but don't know what. It's
not related to the servers since I can move the instance between cluster
nodes and EM still cannot see databases on SQL2. SQL1 moves between nodes
fine and it's databases can be seen via EM.
Not sure if this is a clue, but when I right click on "Databases" and select
"New database", I get this error:
Microsoft SQL-DMO (ODBC SQLState: 22003)
Error 220: Arithmetic overflow error for that data type smallint, value=32826.
Arithmetic overflow error for data type smallint, value=32826.
The statement has been terminated.
I click OK, then I get the standard database properties dialog to create a
database...
Thanks in advance to any help or suggestions.
Regards,
-Daniel
My guess is a problem with DMO. Perhaps re-applying the SQL Server service pack will fix it?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Daniel" <Daniel@.tamc@.discussions.microsoft.com> wrote in message
news:0CB90BA3-08E0-4FA4-9177-6942CDC083BE@.microsoft.com...
> We're running a Win2k Adv Server MS Cluster using two servers with SQL
> instances called SQL1 and SQL2 running on them. For some reason, we can't
> manage SQL2 via Enterprise Manager recently since the databases folder is now
> empty. The databases are there and I can see them via Query Analyzer, but I
> just can't manage them via EM. "Management" folder is also empty, but if I
> refresh (over and over) I sometimes can access the "SQL Server Agent" items.
> Only changes that were made to the servers was to install some security
> patches, but none that related to SQL server. Other SQL servers with the
> same patches work fine. SQL1 instance can be managed with EM just fine.
> I suspect something is wrong with SQL2 instance, but don't know what. It's
> not related to the servers since I can move the instance between cluster
> nodes and EM still cannot see databases on SQL2. SQL1 moves between nodes
> fine and it's databases can be seen via EM.
> Not sure if this is a clue, but when I right click on "Databases" and select
> "New database", I get this error:
> Microsoft SQL-DMO (ODBC SQLState: 22003)
> Error 220: Arithmetic overflow error for that data type smallint, value=32826.
> Arithmetic overflow error for data type smallint, value=32826.
> The statement has been terminated.
> I click OK, then I get the standard database properties dialog to create a
> database...
> Thanks in advance to any help or suggestions.
> Regards,
> -Daniel
>
|||I am seeing these exact symptoms. Cluster, 1 server is showing "No items" in the database folder. Please advise.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||I just ran across this problem today, and what I had to do was drop any databases that weren't in use (offline). I hope this helps anyone else googling for this problem!
-- hbz|||for googlers
no need to drop anything - it's enough to restart that databases whose status causing overflow errors
USE master
GO
DECLARE @.db_name varchar(500)
DECLARE dbn_cursor CURSOR FOR
SELECT [name] FROM sysdatabases;
OPEN dbn_cursor
FETCH NEXT FROM dbn_cursor
INTO @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
select * from sysdatabases where name = @.db_name
IF @.@.ERROR <> 0
BEGIN
declare @.off int
print 'Restarting database '+@.db_name
exec @.off = sp_dboption @.db_name, 'offline', 'TRUE'
IF @.off <> 0
print 'Taking offline failed! Need manual workaround!'
else
begin
declare @.on int
exec @.on = sp_dboption @.db_name, 'offline', 'FALSE'
IF @.on <> 0
print 'Bringing online failed! Need manual workaround!'
else
print 'Restart successfull!'
end
END
FETCH NEXT FROM dbn_cursor
INTO @.db_name
END
CLOSE dbn_cursor
DEALLOCATE dbn_cursor
GO|||I had exactly the same issue, 2x clustered SQL 2000 SP3 boxen, "no items" in
EM DB view, should be 50 or so. Failed over to other node, still no joy.
ran the script & it worked. (edited it to ID the database(s) having problems
first, found it was an old one, then ran full script to restart said DB)
Thank you Alan.
Cheers
Tim
|||maybe you need to re-attach them?
I'd use query analzer and check out master.dbo.sysdatabases and see if that
tells you anything
"Tim" <tim.cox@.noterinaceous.com> wrote in message
news:d8e3b0fef0a846079c0e261264455587@.ureader.com. ..
> I had exactly the same issue, 2x clustered SQL 2000 SP3 boxen, "no items"
in
> EM DB view, should be 50 or so. Failed over to other node, still no joy.
> ran the script & it worked. (edited it to ID the database(s) having
problems
> first, found it was an old one, then ran full script to restart said DB)
> Thank you Alan.
> Cheers
> Tim