场景：
根据用户设置的执行开始时间，结束时间，周期等信息，来创建一个计划。用于调用存储过程，往物理表里刷新数据。
实现方法：
/// <summary> /// 资金报表调用存储过程刷新数据JOB /// </summary> /// <param name="serverName">SQL实例</param> /// <param name="serverUserName">拥有作业的登录名。默认值为 NULL,可解释为当前登录名</param> /// <param name="serverPassword">数据库密码</param> /// <param name="DataBaseName">数据库名称</param> /// <param name="periodUnit">周期单位</param> /// <param name="repeatPeriod">重复周期数</param> /// <param name="repeatPeriod">作业执行的开始日期</param> /// <param name="endMode">有无结束时间</param> /// <param name="endTime">作业执行的停止时间,格式为HHMMSS</param> public static void CreateJob(string serverName, string serverUserName, string serverPassword, string DataBaseName , string periodUnit, string repeatPeriod, DateTime firstExecTime, string endMode, DateTime endTime) { int start_date = Convert.ToInt32(firstExecTime.ToString("yyyyMMdd")); //int start_time = Convert.ToInt32(firstExecTime.ToString("HHmmss")); // 00000 int start_time = 0; int end_date = Convert.ToInt32(endTime.ToString("yyyyMMdd")); int end_time = Convert.ToInt32(endTime.ToString("HHmmss")); int freq_type = 4;//用于指明何时将执行作业的值。freq_type 的数据类型为 int int freq_subday_type = 0;//重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次 int freq_subday_interval = 0;//重复周期数 int active_start_date = 0;//作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD int active_start_time = 0;//作业执行的开始时间,格式为HHMMSS int active_end_date = 0;//作业执行的停止日期,默认为99991231,格式为YYYYMMDD int active_end_time = 0;//作业执行的停止时间,格式为HHMMSS switch (periodUnit) { case "月": freq_type = 16; freq_subday_type = 0x1; //0x1时不执行freq_subday_interval freq_subday_interval = Convert.ToInt32(repeatPeriod); if (endMode.Equals("无结束时间")) //0代表 无结束时间 1 代表重复次数 2 代表结束时间 { active_end_date = 99991231; active_end_time = 235959; } else { active_end_date = end_date; active_end_time = end_time; } active_start_date = start_date; active_start_time = start_time; break; case "周": freq_type = 8; freq_subday_type = 0x1; //0x1时不执行freq_subday_interval freq_subday_interval = Convert.ToInt32(repeatPeriod); if (endMode.Equals("无结束时间")) { active_end_date = 99991231; active_end_time = 235959; } else { active_end_date = end_date; active_end_time = end_time; } active_start_date = start_date; active_start_time = start_time; break; case "日": freq_type = 4; freq_subday_type = 0x1; //0x1时不执行freq_subday_interval freq_subday_interval = Convert.ToInt32(repeatPeriod); if (endMode.Equals("无结束时间")) { active_end_date = 99991231; active_end_time = 235959; } else { active_end_date = end_date; active_end_time = end_time; } active_start_date = start_date; active_start_time = start_time; break; case "时": freq_type = 4; freq_subday_type = 0x8; freq_subday_interval = Convert.ToInt32(repeatPeriod); if (endMode.Equals("无结束时间")) { active_end_date = 99991231; active_end_time = 235959; } else { active_end_date = end_date; active_end_time = end_time; } active_start_date = start_date; active_start_time = start_time; break; case "分": freq_type = 4; freq_subday_type = 0x4; freq_subday_interval = Convert.ToInt32(repeatPeriod); if (endMode.Equals("无结束时间")) { active_end_date = 99991231; active_end_time = 235959; } else { active_end_date = end_date; active_end_time = end_time; } active_start_date = start_date; active_start_time = start_time; break; default: break; } // Job名称：RunPro 步骤名称：RunProStep 数据库名称：DataBaseName
//执行存储过程和sql 参数要设置为： @subsystem=N'TSQL',
string sql = @" IF EXISTS (SELECT JOB_ID FROM MSDB.DBO.SYSJOBS_VIEW WHERE NAME =N'RunPro') EXECUTE MSDB.DBO.SP_DELETE_JOB @JOB_NAME=N'RunPro' declare @jobId uniqueidentifier exec msdb.dbo.sp_add_job @job_name=N'RunPro', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'运行报表存储过程，实现数据刷新。', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'" + serverUserName + @"', @job_id = @jobId OUTPUT exec msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunProStep', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command = N'exec P_CapitalAnalysisdata', @database_name=N'" + DataBaseName + @"', @flags=0 exec msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name = N'报表调度名称', @freq_type= N'" + freq_type + @"', @freq_recurrence_factor = 1, @freq_interval=1, @freq_subday_type= N'" + freq_subday_type + @"', @freq_subday_interval= N'" + freq_subday_interval + @"', @active_start_date = N'" + active_start_date + @"', @active_end_date = N'" + active_end_date + @"', @active_start_time = N'" + active_start_time + @"', @active_end_time = N'" + active_end_time + @"' EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = N'" + serverName + @"'"; string ConnectionString = "Data Source=" + serverName + ";Initial Catalog=" + DataBaseName + ";" + @"User ID=" + serverUserName + @";Password=" + serverPassword; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.Text; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); }