Unable to add the target table for replication

Question

To add a column to the existing replication target table, I ran the following query on the master table.
Drop Table [table name]
Create Table [table name] ~

At that time, it was not removed from the publish target.

After the table was created, to set it up again for publishing, you can check the target table from the Publication Properties window, but you get the following error message and cannot add it to the publish.

In addition, when I go to View Snapshot Agent State and then Start, I get the following error.

I think the reason is that there are still some "dropped" tables in the publication, but
I don't know how to turn it off

Please advise.

Translated with www.DeepL.com/Translator (free version)

Answer 1

Hi @宮内 宏 ,

Firstly this is a Q&A forum in English, suggest you translate your error message to English to make us give you a better help.

Please make sure the option of "Replicate Schema Changes" is set to true as Guoxiong mentioned. Right click on the publisher and click on subscriptions options and you should see ""Replicate Schema Changes" set to true. This takes care of the adding new columns to the replicating tables.

To add a new column to a table and include that column in an existing publication, execute ALTER TABLE <Table> ADD <Column>. By default, the column is then replicated to all Subscribers.

To include an existing column in an existing publication, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - <Publication> dialog box.

Adding an identity column to a published table is not supported.

Please refer to MS document Make Schema Changes on Publication Databases to get more information.

This is an old thread add one column to table for existing transactional replication, hope this could help you.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

AS I mentioned in the comment, if your publication is enabled to replicate DDL, you just add the column in the publisher:

ALTER TABLE [TABLE_NAME] ADD [COLUMN_NAME] [DATA_TYPE];  
GO  

If you want to drop an article from the publication, you can use the stored procedures depending on the type of publication.

For a snapshot or transactional publication:

EXEC sp_droparticle     
 @publication = '[PUBLICATION_NAME]',     
 @article = '[ARTICLE_NAME]',    
 @force_invalidate_snapshot = 1;    
GO  

For a merge publication:

EXEC sp_dropmergearticle     
 @publication = '[PUBLICATION_NAME]',     
 @article = '[ARTICLE_NAME]',    
 @force_invalidate_snapshot = 1;   
GO