Altering properties of a Column fails with: Cannot alter column 'column-name' because it is 'REPLICATED'
Recently had one where Altering column properties or running sp_rename against that column was getting:
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'.
Tried dropping and recreating the publication and subscription several times but did not help. Any ALTER column commands has the same error.
Root Cause: Replication Metadata has got inconsistent. This can happen when one article (i.e. table) is a part of two or more publications. Also could be when publication was enabled for hetero subscribers (like MS Access).
The best way to resolve this issue - run sp_removedbreplication on the database.
Another workaround:creating a dummy column, copying data out of the original column into this dummy column, deleting the original column and then renaming the dummy column to the same name
Another complicated workaround could be:
> Check if the is_non_sql_subscribed property has not reverted back to 1 after snapshot has completed i.e. has the value of 1 when you run:
Select is_non_sql_subscribed,* from sys.columns where name =<<your column name here>>
> If yes, create a dummy publication that has that article. Note the articleId from syspublications (or sysmergepublications).
> Run this command over a DAC connection:
EXEC sys.sp_MSarticlecol @artid=<<artid as above>>, @colid=NULL, @type='nonsqlsub', @operation='drop'