Hi...
I disabled merge replication via the Ent. Mgr wizard and when I try to update a table that was previously part of the replication (either via stored proc or manually in query analyzer) I get this error:
Invalid object name 'dbo.sysmergearticles'
I am guessing that there are still some references or parts of the replication that were somehow left around even after disabling replication. I don't have this table in my database anymore.
Does anyone know how to resolve this issue?
Thanks
- dw
dw,
if the database is no longer involved in replication, either as a subscriber
or as a publisher, then you can run sp_removedbreplication.
HTH,
Paul Ibison
|||It sounds like the merge triggers are still in place. To get this working correctly you should run this script in your former subscription database.
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
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_%')
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(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
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
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 object_name(id), name from sysindexes where name
like 'index%'
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)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
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
DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO
|||Thanks folks. The problem was the triggers. I manually deleted them (I didn't see the replies to this message earlier) and now it works. However, I will be making a copy of the script that Hilary provided.
great! - dw
Has anyone else had similar experiences?
