Add column to subscriber table

• david.blythner

  SSC Enthusiast

  Points: 143

  More actions

  July 25, 2010 at 12:33 am

  #1197914

  Thanks will do just that.

  When done, I will return to this thread and post my findings... Then you can be my sense checker

• spidey73

  SSChasing Mays

  Points: 658

  More actions

  February 2, 2011 at 3:54 am

  #1281323

  I have a question in the same context.

  I have a transactional replication setup. Now I want to add a column only to a table on the subscriber side. (A trigger on this table will fill the new column when a row is added by the replication)

  When is simply do this, the replication fails with the following message:

  Insert Error: Column name or number of supplied values does not match table definition

  Is there a way to accomplish this without having to alter the MS-stored procedures?

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  February 3, 2011 at 5:13 am

  #1281825

  I am not sure how you created the sprocs - I used the defaults and the MS generated insert sproc specifies the columns.

• Srinivas Merugu

  SSC Enthusiast

  Points: 129

  More actions

  March 3, 2025 at 5:09 am

  #4549731

  Find below script to add table into existing replication to generate the snapshot for newly added tables instead of complete snapshot.

  1. **Check value of Anonymous and sync

  sp_helpdistributor

  Use Publication_DB_Name

  GO

  select * from syspublications

  sp_helppublication

  select description,name,allow_anonymous,immediate_sync from syspublications

  2. chnage value

  Use Publication_DB_name

  GO

  EXEC sp_changepublication

  @publication = 'DB_Name',

  @property = N'allow_anonymous',

  @value = 'false'

  GO

  EXEC sp_changepublication

  @publication = 'DB_Name',

  @property = N'immediate_sync',

  @value = 'false'

  3.

  use DB_Name3

  go

  select * from sys.tables where is_replicated=1 and name='Table1' or name='Table2'

  EXEC sp_changepublication

  @publication = 'DB_Name3,

  @property = N'immediate_sync',

  @value = 'True'

  EXEC sp_changepublication

  @publication = 'DB_Name',

  @property = N'allow_anonymous',

  @value = 'True'

  GO

  ---sp_repldone this would mark all pending transactions as completed in blocked log file thus releasing log space. Then could have reinitialize

  --Issue for logreader agent issue

  EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

  EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 0

• March 3, 2025 at 5:14 am

  This was removed by the editor as SPAM