Posted by Erland Sommarskog on 12/03/06 15:42

Benzine (bfausti@gmail.com) writes:
> I recently ran a procedure in Query Analyzer from the Master db to
> clear out all replication information so I could start/recreate it
> again.
>
> After I ran this procedure Enterprise Manager no longer showed the
> registered server in the tree. When I tried to re-register it gave me
> the following message:
>
> "A connection could not be established to ([Database Name])"
>
> "Reason: [SQL-DMO]Sql Server ([Database Name]) must be upgraded to
> version 7.0 or later to be administered by this version of SQL-DMO"
>
> "Please verify that sql is running and check your SQL server
> registration properties (by right click on the ([Database Name]) node)
> and try again."
>...
> Can any one please help me as this is a production machine and needs
> fixing ASAP.

OK, so you've learnt a lesson for the next time: run in test before you
run in production.

You run a script that performs a lot of updates to the system tables,
and in many cases to undocumented columns, and now you wonder why your
server is hosed?

I can't tell if there were was more that was harmful, but this cursor
definitely was:

SELECT name FROM sysobjects WHERE type='P' and (name like 'sp_ins_%'
or name like 'sp_MSdel_%' or name like 'sp_MSins_%'or
name like 'sp_MSupd_%' or name like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@name
select @name='drop procedure ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_procs INTO @name
END

The SELECT hits 30 system procedures on my server, and far from all
are related to replication, for instance sp_updatestats and
sp_updateextendedproperty.

I would recommand that you at first possible maintenance window, detach
all databases and use the rebuildm tool to rebuild the master database.
Or simply reinstall SQL Server. Whatever, don't forget to reapply the
service pack.

If it's difficult to find the time for a reinstall, I suggest that you
open a case with Microsoft. I don't really want to guide you which
scripts to run, as my guidance could be wrong.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]