Fixup Script for conflict tables on a Transactional Replication

This script fixes up the conflict tables generated by a transactional replication subscription, and marks them as system tables.

In SQL Server replication, a subscription to a publication of the type 'Transactional replication with updatable subscriptions' allow edits on the subscriber. Conflicts in those edits (e.g. a record modified at the subscriber and publisher) have to be resolved. The default setting is 'Keep the publisher change'. If a given subscriber is on the losing side of the change, conflicts are stored on the subscribers and can be viewed through the Conflict Viewer on the subscriber. They are stored in conflict tables per publication, per table in the format 'dbo.conflict_{PublicationName}_{TableName}. These tables are system tables.

In the scenario that led to the need for this script, both of the servers was patched to SQL Service Pack 2, and replication stopped and restarted. At some point during the process, the conflict tables showed as User Tables. While replication did work continue successfully, having the tables as User Tables was problematic. For starters, permission grants or scripts that target all user tables risked allowing edits to these replication tables, and the Object Explorer view in SSMS became quite messy.

Before	After

Limited testing for this has occurred - basically confirmed successful insert, update, and delete transactions before and after running this script.

Please note much credit for assisting in finding the underlying symptoms and general brainstorming should go to all the people at @SacSQLUG, and @SQLSoldier

SQL

Edit|Remove

/*

FixupConflictTables

 

Will examine the conflict tables generated by a transactional replication subscription, and mark them 

as system tables if for some reason they are showing up as User Tables

*/

--set this constant to have this script perform the action, otherwise it will output the resulting script only

DECLARE @ExecuteNow bit

SET @ExecuteNow = 0

 

--find the name of the associated publication

DECLARE @Publication sysname

SELECT @Publication = publication FROM dbo.MSreplication_subscriptions

 

PRINT '--Fix up conflict tables for the publication ' + @Publication

 

--buffers to hold SQL statements

DECLARE @SQL nvarchar(4000)

SET @SQL =''

DECLARE @tablename sysname

 

--loop through the tables that look like 'conflict_{Publication}_{tablename) and aren't marked as MS shipped (i.e. system objects)

DECLARE curConflictTables CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.tables WHERE name LIKE 'conflict_' + @Publication + '_%'

    AND is_ms_shipped = 0

 

OPEN curConflictTables

FETCH NEXT FROM curConflictTables INTO @tablename

 

WHILE @@FETCH_STATUS = 0

BEGIN

    --build the sql to convert this to a system table

    SET @SQL = 'sys.sp_MS_marksystemobject ''' + @tablename + ''''

     

    PRINT @SQL

    IF @ExecuteNow = 1 

        EXECUTE sp_executesql @SQL

 

    FETCH NEXT FROM curConflictTables INTO @tablename

END

 

-- close a cursor

CLOSE curConflictTables

DEALLOCATE curConflictTables