removing conflict tables
Brian
and then deleting the publication there seems to be a lot
of conflict tables that are left behind. Is there any way
to delete them? I executed sp_mergereplication_cleanup
but that didn't do the trick.
Thanks
Brian
Hilary Cotter
run this
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
"Brian" <bo'mal...@dep.nyc.gov> wrote in message
news:150e601c3c3fc$d0fc0ca0$a601...@phx.gbl...
Kestas
and you need to make "magic" like that..?
Do you have owner of tables other than "dbo" ?
--
_______________________
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hil...@att.net> wrote in message
news:uv3%233FAxD...@TK2MSFTNGP10.phx.gbl...
Hilary Cotter
in your database.
"Kestas" <kic...@nospammm-mail.lt> wrote in message
news:%234z4FCX...@TK2MSFTNGP09.phx.gbl...
Kestas
when you have replication or when you don't (it they are left out).
What would be interesting to know is why are they becoming "orphaned"... I
am getting such situation only with replication which includes "non dbo
owned" objects and that is not so nice :)
For example I create server login test_user, create databases named KA_TEST
and KA_TEST2 and add user test_user as db_owner.
Then I create a table owned by that user on KA_TEST database:
use [KA_TEST]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[test_user].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test_user].[TEST]
GO
CREATE TABLE [test_user].[TEST] (
[TST_NR] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ROWGUID] uniqueidentifier ROWGUIDCOL NULL
) ON [PRIMARY]
GO
ALTER TABLE [test_user].[TEST] ADD
CONSTRAINT [DF_TEST_ROWGUID] DEFAULT (newid()) FOR [ROWGUID]
GO
Create merge publication:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @dbname = N'KA_TEST', @optname = N'merge
publish', @value = N'true'
GO
use [KA_TEST]
GO
-- Adding the merge publication
exec sp_addmergepublication @publication = N'KA_TEST', @description =
N'Merge publication of KA_TEST database.', @retention = 14, @sync_mode =
N'native', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous =
N'false', @enabled_for_internet = N'false', @centralized_conflicts =
N'true', @dynamic_filters = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@conflict_retention = 14, @keep_partition_changes = N'false',
@allow_subscription_copy = N'false', @allow_synctoalternate = N'false',
@add_to_active_directory = N'false', @max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication = N'KA_TEST',@frequency_type =
8, @frequency_interval = 64, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 1, @frequency_subday = 1,
@frequency_subday_interval = 0, @active_start_date = 0, @active_end_date =
0, @active_start_time_of_day = 223800, @active_end_time_of_day = 0
GO
exec sp_grant_publication_access @publication = N'KA_TEST', @login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'KA_TEST', @login =
N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'KA_TEST', @login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @publication = N'KA_TEST', @article = N'TEST',
@source_owner = N'test_user', @source_object = N'TEST', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd =
N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'test_user', @auto_identity_range =
N'false', @verify_resolver_signature = 0, @allow_interactive_resolver =
N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @publication = N'KA_TEST', @subscriber =
N'SRV', @subscriber_db = N'KA_TEST2', @subscription_type = N'push',
@subscriber_type = N'global', @subscription_priority = 75.000000, @sync_type
= N'automatic', @frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date =
0, @active_end_date = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false',
@offloadagent = 0, @use_interactive_resolver = N'false'
GO
Change the server name from 'SRV' to your server name.
So now I have active replication. If I try to drop it I get an error:
use [KA_TEST]
GO
-- Dropping the merge subscription
exec sp_dropmergesubscription @publication = N'KA_TEST', @subscriber =
N'SRV', @subscriber_db = N'KA_TEST2', @subscription_type = N'push'
GO
-- Dropping the merge publication
exec sp_dropmergepublication @publication = N'KA_TEST'
GO
Change the subscriber server name from 'SRV' to your server name. And here
is the error..
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_user.conflict_KA_TEST_TEST', because it does not
exist in the system catalog.
Server: Msg 14047, Level 16, State 1, Procedure sp_dropmergearticle, Line
385
Could not drop TEST.
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_user.conflict_KA_TEST_TEST', because it does not
exist in the system catalog.
As I understand that is the way how we get orphaned conflict tables.. Or
ther is other way to get such result..?
--
_______________________
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hil...@att.net> wrote in message
news:ufe4cgbx...@TK2MSFTNGP10.phx.gbl...
Hilary Cotter
You only get this when there are objects which are owned by accounts other
than dbo, and if you notice its trying to delete a non existent table, but
the table does exist, its just owned by the dbo.
This bug is not a show stopping error. It falls into a class where there is
a bogus error message raised, but its impact is very limited if at all.
"Kestas" <kic...@nospammm-mail.lt> wrote in message
news:em0B7ggx...@TK2MSFTNGP11.phx.gbl...
Kestas
replication without some stuff (conflict tables for example) left out... And
that causes various problems... for example when recreating replication. And
this is really annoying.
And what is even more important - this bug is "very easy fixable", but not
fixed ;( There is one very simple problem in some system stored procedures
which took me half a day to find and fix it. But of course focus is on Yukon
and lets hope that there will not be such problems there.
--
_______________________
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hil...@att.net> wrote in message
news:ObEQ4Kkx...@tk2msftngp13.phx.gbl...