SQL 2000 EM client stops responding with SQL 7.0 database

Question

SQL 2000 EM client stops responding with SQL 7.0 database

alext

Valued Member

Points: 67
More actions
June 2, 2005 at 8:03 am

#102565

We have a SQL 7.0 and SQL 2000 Server. I have installed SQL 2000 client tools on some workstations but EM freezes when you try to open the list of databases on the registered SQL 7.0 server.

Viewing 3 posts - 1 through 2 (of 2 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Don't have details on your exact situation but this discussion from another forum may help...

SQL7 should be installed first and become the defacto default instance (although it has no concept of instances) and SQL2000 should be installed as a named instance...

**ASCII stupid question, get a stupid ANSI !!!**

Lowell SSC Guru Points: 323518 More actions · Answer 2

Enterprise Manager will exhibit this behavior(seemingly stalled or not responsive) if the db's on the server are closed, and there are a lot of databases; EM must open each database, and each db 's opening costs some time (3-10secs each? times 20 or more databases?) i can guess that the server you are trying to open might be a developer machine and not a production server.

if you change all the db's AUTO_CLOSE property, then EM will open the database folder instantly;

here is a cursor to change that value:

declare

@isql varchar(2000),

@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

open c1

fetch next from c1 into @dbname

While @@fetch_status <> -1

begin

select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

fetch next from c1 into @dbname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!