Snapshot replication fails after adding a column to a table (article)

This post is all about "replicate_dll" property in sp_addpublication, so if you are aware of this property then this is the answer for the problem described in the blog title.

Here is a little more background about this issue:

A column was added to an existing table using the query below:

ALTER TABLE [db].[tblUsers] ADD [BloodGroup] [varchar](10) NULL

After this change, snapshot agent started failing while applying the snapshot.

When we opened the schema file (.sch) from the snapshot folder, the newly added column was missing so the destination table was created without this column. But bcp file has data for this newly column and hence eventually snapshot agent fails with The process could not bulk copy into table '"xxx"

When we tested creating new publication from SSMS, replicate_dll is set to TRUE which means all DDL changes should reflect in the sch file but when scripted out the existing publication for which snapshot delivery started failing, we saw the replicate_dll property was set to FALSE. So somehow this value was changed manually after creating the publication.

So this answers the cause of snapshot delivery failure. At this point, solution is either to select the column from articles tab in publication property in SSMS or drop the column -> Enable replicate_dll -> Add the column again...

Hope that helps!

Regards,

Sakthivel Chidambaram, SQL Server Support