SQL Server Agent Alter Column Error

Replication Agent will fail when attempting to alter a column in the publisher which is part of an index.

For example, let’s say you have table like following:

CREATE TABLE [dbo].[Employees](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [FirstName] [nvarchar](50) NULL,
     [LastName] [varchar](50) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
     [ID] ASC
)
)
 ON [PRIMARY]

And an index as below:

CREATE NONCLUSTERED INDEX [IX_Employees] ON [dbo].[Employees]
(
     [LastName] ASC,
     [FirstName] ASC
)

On this table you have created a publisher and subscriber that will have same the structure and index.  After deploying this, let us assume that there is a requirement to change the length of the data type. Let us assume that you want to change data type of the FirstName column to nvarchar.

Since this column is part of the index, first you will need to drop the index and then perform a column modification.

In the Employees table there won’t be any issues but you will see you replication will fail as shown below.

Though this table in enabled for DDL Replication, replication will fail. Though you drop the index in the publisher, it won’t be replicated to the subscriber. Because of that, although the alter statement was replicated to subscriber, it will fail since that column is part of the index.

Solution

The solution to this is to manually drop the index in the subscriber and let alter statement to replicate to subscriber before creating the subscriber again.

]]>