FIX: Distribution Agent may fail after you add or drop a column for a published article (837231)

WORKAROUND

1. Run the following code to drop all subscriptions to your article:

   USE Publication Database
   GO
   
   EXEC sp_dropsubscription 
   	@publication=N'Publication Name', 
   	@subscriber = N'all', 
   	@article = N'Article Name', 
   	@destination_db = N'all'
   GO
   

2. Run the following code to drop the article from your current transactional publication:

   USE Publication Database
   GO
   
   EXEC sp_droparticle 
   	@publication = N'Publication Name', 
   	@article = 'Article Name',
   	@force_invalidate_snapshot = 1
   GO
   

3. Add your article to your current transactional publication so that the article uses stored procedures to replicate the INSERT command, the UPDATE command, or the DELETE command to the corresponding subscribers. To do this, run the following code:

   USE Publication Database
   GO
   
   EXEC sp_addarticle 
   	@publication = N'Publication Name', 
   	@article = N'Article Name', 
   	@source_owner = N'dbo', 
   	@source_object = N'Source Table Name', 
   	@destination_table = N'Destination Table Name', 
   	@type = N'logbased', 
   	@creation_script = null, 
   	@description = null, 
   	@pre_creation_cmd = N'drop', 
   	@schema_option = 0x00000000000000F3, 
   	@status = 16, 
   	@vertical_partition = N'false', 
   	@ins_cmd = N'sp_MSins_Article Name', 
   	@del_cmd = N'sp_MSdel_Article Name', 
   	@upd_cmd = N'sp_MSupd_Article Name', 
   	@filter = null, 
   	@sync_object = null, 
   	@auto_identity_range = N'false'
   GO
   

   Note Make sure that you modify the following parameters before you run the Transact-SQL statements:
   • @publication: Name of the current publication
   • @article: Name of the new article
   • @source_object: Name of the underlying table that is represented by the new article
   • @destination_table: Name of the destination table that will be created at the Subscriber
   • @ins_cmd: CALL sp_MSins_Article Name
   • @del_cmd: CALL sp_MSdel_Article Name
   • @upd_cmd: MCALL sp_MSupd_Article Name
   • @force_invalidate_snapshot: 1, if a snapshot is already generated for the publication
   Additionally, if your article contains an identity column, and you want SQL Server to manage the identity range handling, you must set the parameter @auto_identity_range to true.
4. Add subscriptions to the article that is added to your transactional publication:
   • For each push subscription that subscribes to your current transactional publication, run Transact-SQL statements that are similar to the following in SQL Query Analyzer at the Publisher on the publication database:

     USE Publication Database
     GO
     
     EXEC sp_addsubscription 
     	@publication = N'Publication Name', 
     	@article = N'Article Name', 
     	@subscriber = N'Subscriber Name', 
     	@destination_db = N'Destination Database', 
     	@sync_type = N'automatic', 
     	@update_mode = N'read only', 
     	@offloadagent = 0, 
     	@dts_package_location = N'distributor'
     GO

     Note Make sure that you modify the following parameters before you run the Transact-SQL statements:
     • @publication: Name of the current publication
     • @article: Name of the new article
     • @subscriber: Name of the Subscriber
     • @destination_db: Name of the destination database that contains the subscription
     Additionally, make sure that the parameters that may change the existing properties of the subscription are not modified.
   • To add subscriptions to your article in the pull subscriptions for all the existing Subscribers to your transactional publication, run Transact-SQL statements that are similar to the following in SQL Query Analyzer at the Publisher on the publication database:

     USE Publication Database
     GO
     
     EXEC sp_refreshsubscriptions 
     	@publication = N'Publication Name'
     GO

5. Run the Snapshot Agent that corresponds to the current transactional publication at the distributor.
6. Run the Distribution Agent that corresponds to each subscription that subscribes to the current transactional publication.