Drop UDF From Merge Publication

Question

Hi,

Running SQL Server 2008 R2. I would like to use sp_dropmergearticle to remove a User-Defined Function from a merge publication using T-SQL.

When I check the sysmergearticles table there is no row for the UDF in question (I can confirm the UDF is listed as a merge article in the SSMS Merge Publication Articles window). Using T-SQL, how would I go about confirming the UDF in question is a merge article prior to executing the SP?

Thanks!

Answer 1

Hi @VDT-7677 ,

Articles can be dropped from a publication at any time, but you must take into account the following behaviors:

• Dropping an article from a publication does not remove the object from the publication database or the corresponding object from the subscription database. Use DROP <Object> to remove these objects if necessary. When you drop an article that is related to other published articles through foreign key constraints, we recommend that you drop the table at the Subscriber manually or by using on-demand script execution: specify a script that includes the appropriate DROP <Object> statements. For more information, see Execute Scripts During Synchronization (Replication Transact-SQL Programming).
• For merge publications with a compatibility level of 90RTM or higher, articles can be dropped at any time, but a new snapshot is required. Additionally:
  1.If an article is a parent article in a join filter or logical record relationship, the relationships must be dropped first, which requires reinitialization.
  2.If an article has the last parameterized filter in a publication, subscriptions must be reinitialized.
• For merge publications with a compatibility level lower than 90RTM, articles can be dropped with no special considerations prior to the initial synchronization of subscriptions. If an article is dropped after one or more subscriptions is synchronized, the subscriptions must be dropped, re-created, and synchronized.
• For snapshot or transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized. For more information about dropping subscriptions, see Subscribe to Publications and sp_dropsubscription (Transact-SQL). sp_dropsubscription allows you to drop a single article from the subscription rather than the entire subscription.

1.Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.

• To drop an article from a publication, see Add Articles to and Drop Articles from a Publication (SQL Server Management Studio) or Delete an Article.

2.After dropping an article from a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters).

• To create a new snapshot, see Create and Apply the Initial Snapshot.
• To create a new snapshot for a merge publication with parameterized filters, see Create a Snapshot for a Merge Publication with Parameterized Filters.

For more details,please see:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/add-articles-to-and-drop-articles-from-existing-publications?view=sql-server-ver16

Best regards
Niko

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".