The “Action if name is in use” field controls what happens to an article’s contents, at the subscriber, when a snapshot is reinitialized. The default setting (with merge replication) is to drop the existing object and create a new one. The problem is that all permissions on that object are also dropped. Fortunately, we can choose another option that will truncate(delete) the data in the object without the removing the permissions. There are a few caveats to this solution. It doesn’t work with articles that are stored procedures (keep the object unchanged is the only other supported option), you have to use transact-SQL to preform the change, and it’s a one way trip (meaning you can’t change back to the previous setting) without recreating the publication.
An alternative solution is to leave the default setting and create a script to reapply the permissions on the object after it has been dropped. In our case, we use this alternative for articles that are stored procedures.
The stored procedure used is called sp_changemergearticle. The parameter we are changing is called the pre_creation_command. This change will require a reinitialization.
Here is the script in generic form.
Use [DatabaseName]sp_changemergearticle @publication = 'PublicationName', @article = 'ArticleName', @property = 'pre_creation_command', @value = 'truncate', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
GO |
Share the post "Modifying the “Action If Name Is in Use Field” for a Merge Replication Article"
Follow