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

Comments

• Anonymous
  November 27, 2012
  Hi Sakthivel, This is really Awesome site.You mentioned here lot of valueble information i liked it and i loved it. Thanks for sharing the information.I appreciate ur's effect.

• Anonymous
  July 21, 2013
  The comment has been removed