{"id":2233,"date":"2012-07-06T03:08:51","date_gmt":"2012-07-06T03:08:51","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2233"},"modified":"2012-07-06T03:30:34","modified_gmt":"2012-07-06T03:30:34","slug":"%e5%90%88%e5%b9%b6%e5%a4%8d%e5%88%b6%e5%90%8c%e6%ad%a5%e6%8e%a7%e5%88%b6","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/07\/06\/%e5%90%88%e5%b9%b6%e5%a4%8d%e5%88%b6%e5%90%8c%e6%ad%a5%e6%8e%a7%e5%88%b6\/","title":{"rendered":"\u5408\u5e76\u590d\u5236\u540c\u6b65\u63a7\u5236"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n\/*\r\n\u590d\u5236\u5206\u53d1\u6267\u884c\u7684\u662fjob,\u63a7\u5236\u590d\u5236\u5206\u53d1\u5c31\u662f\u63a7\u5236\u8fd9\u4e9bjob\u7684\u5f00\u5173\u3002\r\n\r\n--\u542f\u52a8\u5408\u5e76\u590d\u5236\u6240\u6709\u540c\u6b65\r\nEXEC ReplControl @start = 1\r\n--\u5173\u95ed\u6240\u6709\u540c\u6b65\r\nEXEC \u5408\u5e76\u590d\u5236ReplControl @start = 0\r\n*\/\r\n\r\n\r\nCREATE PROCEDURE ReplControl\r\n@start INT --\u662f\u5426\u542f\u52a8\uff1a0\u505c\u6b62\uff0c1\u5f00\u542f\r\nAS \r\nBEGIN \r\n\tDECLARE @execution_status INT,@sql VARCHAR(1000)\r\n\t\r\n\t\/*\u505c\u6b62\u65f6\uff0c\u67e5\u8be2\u6b63\u5728\u6267\u884c\u7684job,\u5f00\u542f\u65f6\uff0c\u67e5\u8be2\u505c\u6b62\u7684job\r\n\t@execution_status:\r\n\t1 \u6b63\u5728\u6267\u884c\u3002\r\n\t4 \u7a7a\u95f2\u3002\r\n\t*\/\r\n\tSET @execution_status = CASE(@start) WHEN 0 THEN 1 ELSE 4 END \r\n\tSET @sql = CASE(@start) WHEN 0 THEN &#39;msdb.dbo.sp_stop_job @job_id = &#39; \r\n\t\t\t\t\t\t\tELSE &#39;msdb.dbo.sp_start_job @job_id = &#39; END \r\n\r\n\tCREATE TABLE #enum_job ( \r\n\tJob_ID uniqueidentifier, \r\n\tLast_Run_Date int, \r\n\tLast_Run_Time int, \r\n\tNext_Run_Date int, \r\n\tNext_Run_Time int, \r\n\tNext_Run_Schedule_ID int, \r\n\tRequested_To_Run int, \r\n\tRequest_Source int, \r\n\tRequest_Source_ID varchar(100), \r\n\tRunning int, \r\n\tCurrent_Step int, \r\n\tCurrent_Retry_Attempt int, \r\n\tState int \r\n\t)       \r\n\tinsert into #enum_job \r\n\texec master.dbo.xp_sqlagent_enum_jobs 1,hello  \r\n\t\t \/*p_sqlagent_enum_jobs &lt;is sysadmin (0 or 1)&gt;, \r\n\t\t\t\t\t\t  &lt;job owner name&gt; \r\n\t\t\t\t\t\t  [, &lt;job id&gt;]\r\n\r\n\tThe first parameter identifies whether you want to return information about all jobs on the server, or just jobs owned by a particular job owner. If you specify &quot;0&quot; for this first parameter, it means you want to return job information for a particular job owner. If you specify a &quot;1,&quot; it means you want information for all jobs. The second parameter identifies the job owner. This parameter is required on all calls to this XP but is only used when you specify &quot;0&quot; for the first parameter. The third and last parameter only needs to be provided if you want to return information about a particular job_id. *\/\r\n\r\n\tDECLARE @Job_ID uniqueidentifier\r\n\tDECLARE @item_table TABLE (Job_ID uniqueidentifier)\r\n\tINSERT INTO @item_table(Job_ID)\r\n\tSELECT A.Job_ID\r\n\tFROM #enum_job a \r\n\tJOIN msdb.dbo.sysjobs j ON a.Job_ID = j.job_id\r\n\tJOIN msdb.dbo.syscategories c ON j.category_id = c.category_id\r\n\tWHERE c.name = &#39;REPL-Merge&#39; AND a.State = @execution_status\r\n\r\n\tDECLARE @tmpSql VARCHAR(1000)\r\n\tWHILE (SELECT COUNT(1) FROM @item_table) &gt; 0\r\n\tBEGIN\r\n\t\tSELECT TOP 1 @Job_ID = Job_ID\r\n\t\tFROM @item_table\r\n\t\tSET @tmpSql = @Sql + QUOTENAME(@Job_ID,&#39;&#39;&#39;&#39;)\r\n\t\tEXEC ( @tmpSql )\r\n\t\t--PRINT @Job_ID\r\n\r\n\t\tDELETE FROM @item_table \r\n\t\tWHERE @Job_ID = Job_ID\r\n\tEND\r\n\tDROP TABLE #enum_job\r\nEND \r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/* \u590d\u5236\u5206\u53d1\u6267\u884c\u7684\u662fjob,\u63a7\u5236\u590d\u5236\u5206\u53d1\u5c31\u662f\u63a7\u5236\u8fd9\u4e9bjob\u7684\u5f00\u5173\u3002 &#8211;\u542f\u52a8\u5408\u5e76\u590d\u5236\u6240\u6709\u540c\u6b65 EXEC Re [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2233","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2233"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2233\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}