Scripting out 'Create Merge Replication' does not include 'processing_order'

Question

Scripting out 'Create Merge Replication' does not include 'processing_order'

Greg Youdale 5

We use Merge Replication and have defined a specific 'processing_order' for the Articles in the Publication. The Articles include tables, functions and stored procedures.
When we want to setup or refresh a test environment, we will script out the 'Create Replication' script from our production environment, such that we can recreate the same Publication in our test environment.
The problem is that the generated 'Create Replication' script does not include the 'processing_order' parameter for any function or stored procedure Article, even though it is a parameter of those Articles. The script generated does however include the 'processing_order' value for table Articles.
This causes us to maintain separate 'processing_order' scripts for each version and having to remember to execute the correct script after the Publication has been (re)created. With 200+ function and stored proc Articles, that extra script takes hours to run !
If the function and stored proc 'processing_order' value was also included in the 'Create Replication' script, it would be a very simple and fail-safe way of recreating the Publication.
Does anybody know why 'processing_order' for functions and stored proc are omitted and can the SQL Team be notified of this omission? ?

0 comments

1 answer

Your answer

Answer 1

Anonymous

Hi @Greg Youdale,

Thanks for your information.

I've checked the article of Understanding Merge Replication article processing order and Checking the Merge Replication properties. We can get the information:

User's image

In addition, I found this article, you can try the methods step by step and check out the links this article provides to resolve your issue.

I am not an expert in this field, if I have any misunderstandings, please feel free to share with us here. Thanks for your understanding. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Greg Youdale 5 Reputation points

2024-05-06T01:58:15.4366667+00:00

Thanks for your time, however, the problem is not with understanding 'processing_order' ... rather, the problem is that the 'processing_order' parameter is not included in the script that is generated when you click Replication -> Generate Scripts -> To create or enable the components.
When you use that generated script to re-create replication on say a test server, the 'processing_order' value is lost ... however, I must clarify that 'processing_order' IS INCLUDED for table Articles, however, missing from stored procedure Articles as shown missing in the sample script below :
exec sp_addmergearticle @publication = N'MinRep', @article = N'usp_fn_CalcRevenue', @source_owner = N'dbo', @source_object = N'usp_fn_CalcRevenue', @type = N'func schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'usp_fn_CalcRevenue', @force_reinit_subscription = 1
Running an Initialisation will fail because the stored procedures have a default processing order of zero and hence are created first before any tables exist, because the tables do have a 'processing_order' and the attempt to create the first stored procedure will fail because it will reference a table that does not yet exists. We have to remember to apply a script manually that updates the 'processing_order' of all the functions and stored procedures ... and that script can take many hours to execute on our 300+ stored proc articles.
If the 'processing_order' for all functions and stored procedure articles was included in the 'Generate Scripts', then all would be fine without having to execute any further script.
I'm wondering why 'processing_order' is not included in the output from 'Generate Scripts' ?
Greg Youdale 5 Reputation points

2024-05-06T01:58:51.4866667+00:00

Thanks for your time, however, the problem is not with understanding 'processing_order' ... rather, the problem is that the 'processing_order' parameter is not included in the script that is generated when you click Replication -> Generate Scripts -> To create or enable the components. When you use that generated script to re-create replication on say a test server, the 'processing_order' value is lost ... however, I must clarify that 'processing_order' IS INCLUDED for table Articles, however, missing from stored procedure Articles as shown missing in the sample script below : exec sp_addmergearticle @publication = N'MinRep', @article = N'usp_fn_CalcRevenue', @source_owner = N'dbo', @source_object = N'usp_fn_CalcRevenue', @type = N'func schema only', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'usp_fn_CalcRevenue', @force_reinit_subscription = 1 Running an Initialisation will fail because the stored procedures have a default processing order of zero and hence are created first before any tables exist, because the tables do have a 'processing_order' and the attempt to create the first stored procedure will fail because it will reference a table that does not yet exists. We have to remember to apply a script manually that updates the 'processing_order' of all the functions and stored procedures ... and that script can take many hours to execute on our 300+ stored proc articles. If the 'processing_order' for all functions and stored procedure articles was included in the 'Generate Scripts', then all would be fine without having to execute any further script. I'm wondering why 'processing_order' is not included in the output from 'Generate Scripts' ?
Anonymous

2024-05-06T08:46:20.6033333+00:00

Hi @Greg Youdale ,

Thanks for your feedback.

Did you have the right steps to create script file? Please check out this article, you can follow the steps in the article and then choose the script file you want to use.

Best regards,

Lucy Chen
Greg Youdale 5 Reputation points

2024-05-07T06:16:19.72+00:00

That is exactly how I generate the script to 'Create' the replication objects ... my point is that the script that is generated DOES include the 'processing_order' parameter value for table Articles, but DOES NOT include the 'processing_order' parameter for any function or stored procedure ... these values are lost and have to be manually reset by executing 'changemergearticle' for every function and stored procedure to get the replication operating correctly ...

Share via

Scripting out 'Create Merge Replication' does not include 'processing_order'

1 answer

Your answer