-- ===========================================================-- script for :-- rest distribution agent profile-- ===========================================================IF OBJECT_ID(N‘msdb.dbo.MSagent_profiles‘) 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_default = 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 IN(                            N‘‘Continue on data consistency errors.‘‘,                            N‘‘遇到数据一致性错误时继续。‘‘)                        THEN profile_id                END),        profile_id_default = MAX(                CASE                    WHEN profile_name IN(                            N‘‘Default agent profile‘‘,                            N‘‘默认代理配置文件‘‘)                        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‘‘遇到数据一致性错误时继续。‘‘,                N‘‘Default agent profile‘‘,                N‘‘默认代理配置文件‘‘)),JOB AS(    SELECT S.name SubServerName,         A.*    FROM dbo.MSdistribution_agents AS A WITH(NOLOCK)    INNER JOIN SYS.SERVERS  AS S WITH(NOLOCK)    on S.server_id=A.subscriber_id    where   A.publication=isnull(@publication_name,A.publication)   and A.publisher_db = isnull( @pub_database_name , publisher_db)     and A.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‘                        ,@pub_database_name = @pub_database_name                        ,@sub_database_name=@sub_database_name                        ,@sub_server_name=@sub_server_name                        ,@publication_name=@publication_name;