07. marts 2005 - 10:58
#4
Jeg har i mellemtiden fundet det her script, som jeg så har stillet pænt op. det ser ud til at virke :
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 -- WHILE
CLOSE list_pubs
DEALLOCATE list_pubs
END --IF EXISTS
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 --WHILE
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
/*
Note :
~ (Bitwise NOT)
& (Bitwise AND)
~4096 equals -4097
Delphi language :
if colstat AND 4096 <> 0 then
colstat := colstat AND not 4096;
*/
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)
DECLARE @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 --WHILE
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
DECLARE @name nVARCHAR(129)
DECLARE @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 -- WHILE
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
DECLARE @name nVARCHAR(129)
DECLARE @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 --WHILE
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 --WHILE
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()
Ny bruger
Nybegynder
Opret
Preview
