Share via


Issues with Updatable Subscriptions for Transactional Replication

Careful with Replication type "Updatable Subscriptions for Transactional Replication" when your application does not use column names in INSERT and SELECT statements and has database triggers

NOTE:This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Problem : Configuration of replication type "Updatable Subscriptions for Transactional Replication" made application stop working because of column names not defined in some of INSERT/SELECT statements and "NOT FOR REPLICATION" option not defined in Triggers.

Resolution : Replication needs to be removed ASAP and dropping of extra columns and constraints created by Replication.

Please follow below steps to remove replication and drop constraints/columns,

1. Remove Subscription and Publication (Please follow the below link)

https://support.microsoft.com/default.aspx/kb/324401

2. Removing Columns and constraints from table/articles created by Replication 

1. Run the below query on publisher database, copy the output and execute the output on publisher database to remove constraints prefixed with "msrepl_tran_version".
    SELECT 'ALTER TABLE '+ s.name+ '.'+ OBJECT_NAME(o.parent_object_id) + '    DROP CONSTRAINT ' + o.name

    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.name LIKE '%MSrepl_tran_version_default%'

  2. Run the below query on publisher database, copy the output and execute the output on publisher database to remove columns named "msrepl_tran_version".

    SELECT 'ALTER TABLE '+TABLE_SCHEMA+'.'+TABLE_NAME + ' DROP COLUMN msrepl_tran_version'

FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='msrepl_tran_version'

Above steps can be followed for subscriber database, if needed.

Suggestion : before configuring this replication type, please go through the below link to understand more about this and considering for your environment
https://msdn.microsoft.com/en-us/library/ms151718.aspx