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.
The solution to this is to manually drop the index in the subscriber
and let alter statement to replicate to subscriber before creating the