Enterprise Manager not finding databases locally (1 Viewer)

[Geoff Convery]

We have a SQL Server 2000 box which supports a number of SQL Server databases. To administer these databases we have been using both the local Enterprise Manager on the server (via Terminal Services) and PC-installed copies of Enterprise Manager accessing them the server as a remote server. The system has developed a fault which has us puzzled.
The remote installations of Enterprise Manager still work perfectly as do installations of Enterprise Manager on other servers used over Terminal Services but the local copy will no longer connect to them. When we try an error message is displayed saying that
"A connection could not be established to (local)"
"Reason:Invalid connection"
"ConnectionOpen(ParseConnectParams())
There is nothing obvious wrong with the Local Connection registration except that the server name is preceded by two backslashes (\\Servername).
We have tried to delete and re-establish the local registration without success.
Anybody got any ideas?

 

[pdx_man]

What is returned by:

SELECT srvname
FROM master.dbo.sysservers
WHERE srvid = 0

is it the correct name of the instance?

Was there ever a renaming of the server or of the SQL Server instance?

** Edit
I wanted to add that you should always have the server registered with its name. Don't now if you are doing any replication, but I know that working with a registered instance of (local) gives replication a rash when trying to modify anything about it.

 

[Geoff Convery]

Hi PDXMan,
Thanks for the suggestion. I tried using the Query Analyser to run your query but it wouldn't connect to the server (a bit like "There's a hole in my bucket dear Liza")
The response codes were "Msg14 Level16, State1" and referred to [ODBC Server Driver] [TCP/IP Sockets] invalid connection
It's gone dark this side of the pond so I'm going home, but I'll look up the error messages on Microsoft tomorrow. Probably speak again!!
Thanks a lot

 

[pdx_man]

You can also run this in any Enterprise Manager ... Table Design View ... SQL and paste and run the text.

Be sure to re-register the server in EM, though.

 

[Geoff Convery]

Running the SQL from another computer returned the correct name. Looking at the list of available servers, though, we have two available servers called "(local)" with the brackets, one called "\\SERVERNAME" with the backslashes and one called "local" without the brackets. What we don't have locally but do have on remote sessions is one called "SERVERNAME" without the backslashes. If I try to type "SERVERNAME" into the registration box, however, the registration facility says that a connection with that name already exists. All very strange!!