2

I want to run the following rename

EXECUTE sp_rename N'dbo.Semesters.IsPublic', N'Tmp_ShowNCs', 'COLUMN'

I get the error

Msg 4928, Level 16, State 1, Procedure sp_rename, Line 547
Cannot alter column 'IsPublic' because it is 'REPLICATED'.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I have enabled change data capture (CDC) for this table. Probably thats why this column is "replicated". SSMS shows replicated = yes.

So I want to know if this is normal or a bug. I want to keep CDC on but rename this column. Do I have options other than deleting existing CDC information and re-enabling CDC?

usr
  • 168,620
  • 35
  • 240
  • 369
  • CDC is only for tracking DDL changes - it doesn't stop them. You're attempting to alter a column in a table that is marked for replication. Check the Replication Monitor for more info: http://msdn.microsoft.com/en-us/library/ms151780.aspx – OMG Ponies Feb 21 '10 at 19:05
  • I suspect that CDC is the problem because it seems to be implemented on top of the replication infrastructure. – usr Feb 21 '10 at 19:07
  • 1
    Just a clarification... CDC tracks DML changes, not DDL changes as OMG Ponies stated. – Derek Morrison Mar 23 '11 at 08:36

1 Answers1

1

you did in wrong order. You have to turn off CDC on table (CDC on DB is turned on), then change column, then turn On CDC on table. To undo your problem you have to turn on CDC on DB, then on table, turn off CDC on table, rename column, turn on CDC on table.

showstoppa
  • 26
  • 1
  • 1
    This is a workaround to the error, but it doesn't seem to fully address the original question: "Do I have options other than deleting existing CDC information and re-enabling CDC?". Turning off CDC on a table deletes all of the CDC data for it (I think). – Derek Morrison Mar 23 '11 at 08:29