Replication: Cannot Alter Column ‘column_name’ because it is Replicated

  • Article

Problem:

I wanted to change the schema of a table which was published in my replication topology. I removed the table from two publication in which it was being published and tried changing the column name. This failed with the following error:

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 4928, Level 16, State 1, Procedure sp_rename, Line 520
Cannot alter column '<column-name>' because it is 'REPLICATED'.

Cause:

Since the metadata at the publisher was not properly cleaned when a non sql subscriber was deleted, some article column's retained the is_non_sql_subscribed property to true. Due to this, I was not able to make schema changes on the tables where their columns retained this property.

Solution / Workaround:

Solution would be to drop the publications and then alter the table(s).

The workaround is to do the following:

a. Backup the database in question

b. Open a dedicated admin connection

c. Check that the columns of the tables in question have the “is_non_sql_subscribed” property to true:

Select is_non_sql_subscribed,* from sys.columns (filter through object id)

d. Remove the table from one publication

e. Execute:

Select artid from sysarticles where name=’table_name’

f. Execute:

Exec sys.sp_MSarticlecol @artid=, @colid=NULL, @type='nonsqlsub', @operation='drop' with previous artid

g. Execute the alter table statement

h. Add the table to the second publication