Enterprise Manager reporting wrong server version

[Benzine]

I am running MS SQL 2000.

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."

I ran the following procedure:

<code>
exec sp_configure N'allow updates', 1
go
reconfigure with override
go

DECLARE @name varchar(129)
DECLARE @username varchar(129)
DECLARE @insname varchar(129)
DECLARE @delname varchar(129)
DECLARE @updname varchar(129)
set @insname=''
set @updname=''
set @delname=''

DECLARE list_triggers CURSOR FOR
select distinct replace(artid,'-',''), sysusers.name from
sysmergearticle s,sysobjects, sysusers where
sysmergearticle s.objid=sysobje cts.id
and sysusers.uid=sy sobjects.uid

OPEN list_triggers

FETCH NEXT FROM list_triggers INTO @name, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping trigger ins_' +@name
select @insname='drop trigger ' +@username+'.in s_'+@name
exec (@insname)
PRINT 'dropping trigger upd_' +@name
select @updname='drop trigger ' +@username+'.up d_'+@name
exec (@delname)
PRINT 'dropping trigger del_' +@name
select @delname='drop trigger ' +@username+'.de l_'+@name
exec (@updname)
FETCH NEXT FROM list_triggers INTO @name, @username
END

CLOSE list_triggers
DEALLOCATE list_triggers
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY( id,
N'IsUserTable')
= 1) begin DECLARE @name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications

OPEN list_pubs

FETCH NEXT FROM list_pubs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@name
EXEC sp_dropsubscrip tion @publication=@n ame, @article='all',
@subscriber
='all'
EXEC sp_droppublicat ion @name
FETCH NEXT FROM list_pubs INTO @name
END

CLOSE list_pubs
DEALLOCATE list_pubs
end
GO


DECLARE @name varchar(129)
DECLARE list_replicated _tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticle s

OPEN list_replicated _tables

FETCH NEXT FROM list_replicated _tables INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@name
--select @name='drop Table ' + @name
EXEC sp_msunmarkrepl info @name
FETCH NEXT FROM list_replicated _tables INTO @name
END

CLOSE list_replicated _tables
DEALLOCATE list_replicated _tables

GO

UPDATE syscolumns set colstat = colstat & ~4096 WHERE colstat &4096
<>0
GO

UPDATE sysobjects set replinfo=0
GO

DECLARE @name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name like 'syncobj_%'
or
name
like 'ctsv_%' or name like 'tsvw_%' or name like 'ms_bi%')

OPEN list_views

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

CLOSE list_views
DEALLOCATE list_views

GO

DECLARE @name nvarchar(129)
DECLARE list_procs CURSOR FOR
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

CLOSE list_procs
DEALLOCATE list_procs

GO

DECLARE @name nvarchar(129)
DECLARE list_conflict_t ables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name like '_onflict%'

OPEN list_conflict_t ables

FETCH NEXT FROM list_conflict_t ables INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@name
select @name='drop Table ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_conflict_t ables INTO @name
END

CLOSE list_conflict_t ables
DEALLOCATE list_conflict_t ables

GO

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

GO


Declare @name nvarchar(200), @constraint nvarchar(200)
DECLARE list_rowguid_co nstraints CURSOR FOR
select sysusers.name+' .'+object_name( sysobjects.pare nt_obj),
sysobjects.name
from sysobjects, syscolumns,sysu sers where sysobjects.type ='d' and
syscolumns.id=s ysobjects.paren t_obj
and sysusers.uid=sy sobjects.uid
and syscolumns.name ='rowguid'

OPEN list_rowguid_co nstraints

FETCH NEXT FROM list_rowguid_co nstraints INTO @name, @constraint WHILE
@@FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid constraints ' +@name
select @name='ALTER TABLE ' + rtrim(@name) + ' DROP CONSTRAINT '
+@constraint
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_co nstraints INTO @name, @constraint END

CLOSE list_rowguid_co nstraints
DEALLOCATE list_rowguid_co nstraints

GO

Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_in dexes CURSOR FOR
select sysusers.name+' .'+object_name( sysindexes.id), sysindexes.name
from
sysindexes, sysobjects,sysu sers where sysindexes.name like 'index%' and
sysobjects.id=s ysindexes.id and sysusers.uid=sy sobjects.uid

OPEN list_rowguid_in dexes

FETCH NEXT FROM list_rowguid_in dexes INTO @name, @constraint WHILE
@@FETCH_STATUS = 0 BEGIN
PRINT 'dropping rowguid indexes ' +@name
select @name='drop index ' + rtrim(@name ) + '.' +@constraint
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_in dexes INTO @name, @constraint END

CLOSE list_rowguid_in dexes
DEALLOCATE list_rowguid_in dexes
GO


Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_ms_bidi_ta bles CURSOR FOR
select sysusers.name+' .'+sysobjects.n ame from
sysobjects,sysu sers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sy sobjects.uid
and sysobjects.type ='u'

OPEN list_ms_bidi_ta bles

FETCH NEXT FROM list_ms_bidi_ta bles INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping ms_bidi ' +@name
select @name='drop table ' + rtrim(@name )
EXEC sp_executesql @name
FETCH NEXT FROM list_ms_bidi_ta bles INTO @name
END

CLOSE list_ms_bidi_ta bles
DEALLOCATE list_ms_bidi_ta bles

GO

Declare @name nvarchar(129)
DECLARE list_rowguid_co lumns CURSOR FOR
select sysusers.name+' .'+object_name( syscolumns.id) from syscolumns,
sysobjects,sysu sers where syscolumns.name like 'rowguid' and
object_Name(sys objects.id) not like 'msmerge%'
and sysobjects.id=s yscolumns.id
and sysusers.uid=sy sobjects.uid
and sysobjects.type ='u' order by 1


OPEN list_rowguid_co lumns

FETCH NEXT FROM list_rowguid_co lumns INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@name
select @name='Alter Table ' + rtrim(@name ) + ' drop column rowguid'
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_co lumns INTO @name
END

CLOSE list_rowguid_co lumns
DEALLOCATE list_rowguid_co lumns
go

Declare @name nvarchar(129)
DECLARE list_views CURSOR FOR

select name From sysobjects where type ='v' and status =-1073741824 and
name
<>'sysmergeexte ndedarticlesvie w'

OPEN list_views

FETCH NEXT FROM list_views INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication views ' +@name
select @name='drop view ' + rtrim(@name )
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_views INTO @name
END

CLOSE list_views
DEALLOCATE list_views
go
Declare @name nvarchar(129)
DECLARE list_procs CURSOR FOR

select name From sysobjects where type ='p' and status = -536870912

OPEN list_procs

FETCH NEXT FROM list_procs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping replication procedure ' +@name
select @name='drop procedure ' + rtrim(@name )
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_procs INTO @name
END

CLOSE list_procs
DEALLOCATE list_procs

go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergepublica tions]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublica tions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscri ptions]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscri ptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscription s]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM syssubscription s
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdat es]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdat es
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY( id,
N'IsUserTable')
= 1)
DELETE FROM systranschemas
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergearticle s]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticle s
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaa rticles]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaa rticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscri ptions]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscri ptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY( id,
N'IsUserTable') =
1)
DELETE FROM sysarticles
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticl es]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticl es
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY( id,
N'IsUserTable')
= 1)
DELETE FROM syspublications
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemac hange]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemac hange
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetf ilters]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetf ilters
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapsh otjobs]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapsh otjobs
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapsh otviews]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapsh otviews
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsync partners]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsync partners
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_content s]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_content s
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_delete_ conflicts]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_delete_ conflicts
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_errorli neage]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorli neage
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhist ory]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhist ory
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinf o]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinf o
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombsto ne]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombsto ne
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSpub_identity_ range]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSpub_identity_ range
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSrepl_identity _range]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSrepl_identity _range
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSreplication_s ubscriptions]') and
OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_s ubscriptions
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSsubscription_ agents]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
DELETE FROM MSsubscription_ agents
GO

if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscription s]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
create table syssubscription s (artid int, srvid smallint, dest_db
sysname,
status tinyint, sync_type tinyint, login_name sysname,
subscription_ty pe
int, distribution_jo bid binary, timestamp timestamp,updat e_mode
tinyint,
loopback_detect ion tinyint, queued_reinit bit)

CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[name] [sysname] NOT NULL ,
[pubid] [int] IDENTITY (1, 1) NOT NULL ,
[repl_freq] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_method] [tinyint] NOT NULL ,
[snapshot_jobid] [binary] (16) NULL ,
[independent_age nt] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_int ernet] [bit] NOT NULL ,
[allow_push] [bit] NOT NULL ,
[allow_pull] [bit] NOT NULL ,
[allow_anonymous] [bit] NOT NULL ,
[immediate_sync_ ready] [bit] NOT NULL ,
[allow_sync_tran] [bit] NOT NULL ,
[autogen_sync_pr ocs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tr an] [bit] NOT NULL ,
[snapshot_in_def aultfolder] [bit] NOT NULL ,
[alt_snapshot_fo lder] [nvarchar] (255) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[pre_snapshot_sc ript] [nvarchar] (255) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[post_snapshot_s cript] [nvarchar] (255) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[compress_snapsh ot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirector y] [nvarchar] (255) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscript ion_copy] [bit] NOT NULL ,
[centralized_con flicts] [bit] NULL ,
[conflict_retent ion] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_l evel] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarti clesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script , NULL, description,
dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cm d, status, NULL,
type,
NULL,
schema_option, dest_owner
FROM sysschemaarticl es
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[sysarticles]
GO

CREATE TABLE [dbo].[sysarticles] (
[artid] [int] IDENTITY (1, 1) NOT NULL ,
[columns] [varbinary] (32) NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cm d] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysschemaarticl es]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticl es]
GO

CREATE TABLE [dbo].[sysschemaarticl es] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cm d] [tinyint] NOT NULL ,
[status] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[schema_option] [binary] (8) NULL ,
[dest_owner] [sysname] NULL
) ON [PRIMARY]
GO




declare @dbname varchar(130)
select @dbname ='sp_replicatio ndboption
'+char(39)+db_n ame()+char(39)+ ',''merge publish'',''fal se'''
exec (@dbname)
select @dbname ='sp_replicatio ndboption
'+char(39)+db_n ame()+char(39)+ ',''publish'',' 'false'''
exec (@dbname)

reconfigure with override
go


select db_name()
</code>

Can any one please help me as this is a production machine and needs
fixing ASAP.

Regards,

Ben

[Erland Sommarskog]

Re: Enterprise Manager reporting wrong server version

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_updateextend edproperty.

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@sommarsk og.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

[Benzine]

Re: Enterprise Manager reporting wrong server version

Thank you for your reply.

Unfortunately I didn't have the luxury of a test environment, so I
took a big risk I know. Thankfully we had backups running on Veritas, I
restored to a previous version of the master and msdb databases and
this fixed my problem.



Erland Sommarskog wrote:

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_updateextend edproperty.
>
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@sommarsk og.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/prodinf...ons/books.mspx