Share via

rename a column in replicated table using sp_rename

szh8866-8371 140 Reputation points
2024-02-09T02:10:34.1833333+00:00

The table FTPsite is replicated from Server A to Server B. when I renamed a column of Test to MyTest by runnig sp_rename 'FtpSite.test','Mytest','COLUMN'; on server A. an error occur Msg 15051, Level 11, State 1, Procedure sp_rename, Line 278 [Batch Start Line 7] Cannot rename the table because it is published for replication. The replication is set up to allow schema change. run the statement SELECT replicate_ddl FROM syspublications; it return 1. This table has huge records. I don't want to take the table out from the replication, rename the column and add it back and take a new snapshot. How to rename the column without a new snapshot?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,449 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Greg Low 1,930 Reputation points Microsoft Regional Director
    2024-02-09T04:22:36.36+00:00

    You need to do two things:

    • Make sure it's configured for DDL replication (publication option)
    • Make sure the snapshot agent isn't running.

    For details of the second, see my blog post here: https://blog.greglow.com/2020/04/30/fix-sql-server-replication-cannot-alter-column-because-it-is-replicated/

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.