|
|
Posted by Benzine on 12/01/06 05:11
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
sysmergearticles,sysobjects, sysusers where
sysmergearticles.objid=sysobjects.id
and sysusers.uid=sysobjects.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+'.ins_'+@name
exec (@insname)
PRINT 'dropping trigger upd_' +@name
select @updname='drop trigger ' +@username+'.upd_'+@name
exec (@delname)
PRINT 'dropping trigger del_' +@name
select @delname='drop trigger ' +@username+'.del_'+@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_dropsubscription @publication=@name, @article='all',
@subscriber
='all'
EXEC sp_droppublication @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 sysmergearticles
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_msunmarkreplinfo @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_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables 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_tables INTO @name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @name nvarchar(200), @constraint nvarchar(200)
DECLARE list_rowguid_constraints CURSOR FOR
select sysusers.name+'.'+object_name(sysobjects.parent_obj),
sysobjects.name
from sysobjects, syscolumns,sysusers where sysobjects.type ='d' and
syscolumns.id=sysobjects.parent_obj
and sysusers.uid=sysobjects.uid
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints 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_constraints INTO @name, @constraint END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select sysusers.name+'.'+object_name(sysindexes.id), sysindexes.name
from
sysindexes, sysobjects,sysusers where sysindexes.name like 'index%' and
sysobjects.id=sysindexes.id and sysusers.uid=sysobjects.uid
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes 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_indexes INTO @name, @constraint END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_ms_bidi_tables CURSOR FOR
select sysusers.name+'.'+sysobjects.name from
sysobjects,sysusers where sysobjects.name like 'ms_bi%'
and sysusers.uid=sysobjects.uid
and sysobjects.type='u'
OPEN list_ms_bidi_tables
FETCH NEXT FROM list_ms_bidi_tables 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_tables INTO @name
END
CLOSE list_ms_bidi_tables
DEALLOCATE list_ms_bidi_tables
GO
Declare @name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,
sysobjects,sysusers where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysusers.uid=sysobjects.uid
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns 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_columns INTO @name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
go
Declare @name nvarchar(129)
DECLARE list_views CURSOR FOR
select name From sysobjects where type ='v' and status =-1073741824 and
name
<>'sysmergeextendedarticlesview'
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].[sysmergepublications]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergepublications
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM syssubscriptions
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysarticleupdates
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].[sysmergearticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergearticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemaarticles
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubscriptions
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].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysschemaarticles
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].[sysmergeschemachange]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergeschemachange
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM sysmergesubsetfilters
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotjobs
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSdynamicsnapshotviews
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_altsyncpartners
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_contents
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_errorlineage]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_errorlineage
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_genhistory
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_replinfo
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSmerge_tombstone
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_subscriptions]') and
OBJECTPROPERTY(id,
N'IsUserTable') = 1)
DELETE FROM MSreplication_subscriptions
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].[syssubscriptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
create table syssubscriptions (artid int, srvid smallint, dest_db
sysname,
status tinyint, sync_type tinyint, login_name sysname,
subscription_type
int, distribution_jobid binary, timestamp timestamp,update_mode
tinyint,
loopback_detection tinyint, queued_reinit bit)
CREATE TABLE [dbo].[syspublications] (
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_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_agent] [bit] NOT NULL ,
[immediate_sync] [bit] NOT NULL ,
[enabled_for_internet] [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_procs] [bit] NOT NULL ,
[retention] [int] NULL ,
[allow_queued_tran] [bit] NOT NULL ,
[snapshot_in_defaultfolder] [bit] NOT NULL ,
[alt_snapshot_folder] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[pre_snapshot_script] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[post_snapshot_script] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[compress_snapshot] [bit] NOT NULL ,
[ftp_address] [sysname] NULL ,
[ftp_port] [int] NOT NULL ,
[ftp_subdirectory] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[ftp_login] [sysname] NULL ,
[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[allow_dts] [bit] NOT NULL ,
[allow_subscription_copy] [bit] NOT NULL ,
[centralized_conflicts] [bit] NULL ,
[conflict_retention] [int] NULL ,
[conflict_policy] [int] NULL ,
[queue_type] [int] NULL ,
[ad_guidname] [sysname] NULL ,
[backward_comp_level] [int] NOT NULL
) ON [PRIMARY]
GO
create view sysextendedarticlesview
as
SELECT *
FROM sysarticles
UNION ALL
SELECT artid, NULL, creation_script, NULL, description,
dest_object,
NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL,
type,
NULL,
schema_option, dest_owner
FROM sysschemaarticles
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_General_CP1_CI_AS
NULL
,
[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dest_table] [sysname] NOT NULL ,
[filter] [int] NOT NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [tinyint] NOT NULL ,
[status] [tinyint] NOT NULL ,
[sync_objid] [int] NOT NULL ,
[type] [tinyint] NOT NULL ,
[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_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].[sysschemaarticles]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sysschemaarticles]
GO
CREATE TABLE [dbo].[sysschemaarticles] (
[artid] [int] NOT NULL ,
[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[dest_object] [sysname] NOT NULL ,
[name] [sysname] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[pre_creation_cmd] [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_replicationdboption
'+char(39)+db_name()+char(39)+',''merge publish'',''false'''
exec (@dbname)
select @dbname ='sp_replicationdboption
'+char(39)+db_name()+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
[Back to original message]
|