Reset the distributor agent's options for consistent error handling

The following script sets the Data Consistency error handling option for the distribution Agent Based on replication's publication_name, subscription server, publish table name, and subscription table name (default processing method, skipping Data Consistency processing ).

-- ===================================================== ============================== -- Script: -- rest distribution agent profile -- ==================================== ==================================== if object_id (n' MSDB. DBO. comment ') is null return; declare @ SQL nvarchar (max), @ flag_default bit, @ publication_name sysname, @ pub_database_name sysname, @ sub_database_name sysname, @ sub_server_name sysname; select @ flag_de Fault = 1 -- 1: Default 0: continue on data consistency errors, @ publication_name = NULL -- NULL: All, @ pub_database_name = NULL -- NULL: All, @ sub_database_name = NULL -- NULL: all, @ sub_server_name = NULL -- NULL: All; withdb as (select * From sys. databases with (nolock) Where is_distributor = 1), SQL as (select SQL = n'use' + quotename (name) + N '; withpf as (select profile_id_continue = max (case when profile_name I N (n''continue on data consistency errors. '', n'' continues when a data consistency error occurs. '') Then profile_id end), profile_id_default = max (case when profile_name in (n''default agent profile'', n''default proxy configuration file '') Then profile_id end) from MSDB. DBO. msagent_profiles with (nolock) Where agent_type = 3 and profile_name in (n''continue on data consistency errors. '', n'' continues when a data consistency error occurs. '', N' default agent profile'', n' default agent configuration file ''), job as (select S. name subservername,. * From DBO. msdistribution_agents as a with (nolock) Inner join sys. servers as s with (nolock) on S. server_id =. subscriber_id where. publication = isnull (@ publication_name,. publication) and. publisher_db = isnull (@ pub_database_name, publisher_db) and. subscriber_db = isnull (@ sub_database_name, subscriber_db) and S. name = isnull (@ sub_server_name, S. name) Select job. publication, Job. subservername, Job. publisher_db, Job. subscriber_db, agent_id = job. ID, job_name = job. name, PF. profile_id_continue, PF. profile_id_default, SQL _job_stop = n' exec MSDB. DBO. sp_stop_job @ job_name = n'' + quotename (job. name, N '''), SQL _job_profile = n' exec ''+ quotename (db_name () + N ''. DBO. sp_update_agent_profile @ agent_type = 3, @ agent_id = ''+ rtrim (job. ID) + N', @ profile_id = ''' + case when @ flag_default = 1 then n' + rtrim (PF. profile_id_default), 'else n' + rtrim (PF. profile_id_continue), 'end + N' SQL _job_start = n' exec MSDB. DBO. sp_start_job @ job_name = n'' + quotename (job. name, n''' ''') from job, PF '+ case when @ flag_default = 1 then n' where job. profile_id <> PF. profile_id_default 'else n' where job. profile_id = PF. profile_id_default 'end + N'; 'from dB) Select @ SQL = sqlfrom SQL; print (@ SQL); Exec sys. sp_executesql @ SQL, n' @ pub_database_name sysname, @ sub_database_name sysname, @ sub_server_name sysname, @ publication_name sysname, @ response = @ pub_database_name, @ response = @ response, @ sub_server_name = @ sub_server_name, @ publication_name = @ publication_name;

View code

 

Reset the distributor agent's options for consistent error handling