1

I want to change the length of a replicated column. I ran this:

exec  sp_articlecolumn 
    @publication = N'DIONLINE-SQL2014'
    , @article = N'Valuation'
    , @column = N'WallConstruction'
    , @operation = N'drop'
    , @force_invalidate_snapshot = 1
    , @force_reinit_subscription = 1

Result: (1 row(s) affected)

Then this

alter table Valuation.Valuation
  alter column WallConstruction varchar(255) null

Result: Msg 4928, Level 16, State 1, Line 1 Cannot alter column 'WallConstruction' because it is 'REPLICATED'.

I've successfully used a very similar approach to perform a very similar task - altering the Valuation.Valuation.RoofConstruction column (except I removed that column from the publication via the UI) - so I'm puzzled why this won't work.

I plan eventually to add the column back into the publication.

What's going on here, or where do I look to find out?

Edit

Luckily the column I was trying to alter had not yet received any values, so I was able to do this:

alter table Valuation.Valuation
  drop column WallConstruction

alter table Valuation.Valuation
  add WallConstruction varchar(255) null

but that doesn't really seem like the most useful answer. So if anyone has a better solution...

3
  • Is this for transactional replication? If yes, you don't need to remove column from replication using exec sp_articlecolumn. Direct alter should work as by default replicate schema changes is true. The reason i think it works using UI is because it drops subscription as well when you do it from there. But when you are using script you are only dropping it from replication and not its subscription. You need to test it though before jumping to any conclusion. To know it better run profiler to see what command it runs when you do it from UI.
    – SQLPRODDBA
    May 29 '16 at 5:32
  • @OutstandingBill .. Check my answer here. Let me know if it helps or not :-)
    – Kin Shah
    May 29 '16 at 19:45
  • This might seem strange, but even if you've never enabled CDC on the database, this has been known to clear the bit: enable CDC on the database and table, then disable CDC on the database and table.
    – stacylaray
    May 30 '16 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.