/// <summary> /// Save any changes /// </summary> /// <returns>True if any changes were commited</returns> public bool ApplyChanges() { bool changesMade = UpdateSourceSchedule(); // save the changes. if (this.alreadyCreated) { source.Alter(); } else { source.Create(); // retrieving source.ID after creation would throw if the // server was in CaptureSql mode. This is because the schedule // id is not generated while capturing sql. Thus, we only query // id and set the created flag to true only when the smo object // is actually created and not scripted. Microsoft.SqlServer.Management.Smo.Server svr = null; if (this.parentJob != null && this.parentJob.Parent != null && this.parentJob.Parent.Parent != null) { svr = this.parentJob.Parent.Parent as Microsoft.SqlServer.Management.Smo.Server; } if (svr == null || SqlExecutionModes.CaptureSql != (SqlExecutionModes.CaptureSql & svr.ConnectionContext.SqlExecutionModes)) { this.id = source.ID; this.Created = true; } } return(changesMade); }
private void TgGenerateJob() { if (_DwServer.JobServer.Jobs.Contains(TgJobNameExecStoredProcedureETL)) { _TgJob = _DwServer.JobServer.Jobs[TgJobNameExecStoredProcedureETL]; _TgJob.Drop(); } _TgJob = new Job(_DwServer.JobServer, TgJobNameExecStoredProcedureETL); _TgJob.Create(); JobStep aJobStep = new JobStep(_TgJob, "Execute ETL"); aJobStep.DatabaseName = DwDbName; aJobStep.SubSystem = AgentSubSystem.TransactSql; aJobStep.Command = string.Format("Exec {0}", TgExecStoredProcedureETL); aJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess; aJobStep.OnFailAction = StepCompletionAction.QuitWithFailure; aJobStep.Create(); JobSchedule SQLSchedule = new JobSchedule(_TgJob, "Execute ETL daily at night"); SQLSchedule.FrequencyTypes = FrequencyTypes.Daily; SQLSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once; SQLSchedule.ActiveStartTimeOfDay = new TimeSpan(0, 16, 0); SQLSchedule.FrequencyInterval = 1; SQLSchedule.ActiveStartDate = DateTime.Now; SQLSchedule.ActiveEndDate = new DateTime(9999, 12, 30); SQLSchedule.ActiveEndTimeOfDay = new TimeSpan(23, 59, 0); SQLSchedule.Create(); _TgJob.ApplyToTargetServer(DwDbServerName); _TgJob.Refresh(); }
static void Main(string[] args) { Server server = new Server("."); // Get instance of SQL Agent SMO object JobServer jobServer = server.JobServer; Job job = null; JobStep step = null; JobSchedule schedule = null; // Create a schedule schedule = new JobSchedule(jobServer, "Schedule_1"); schedule.FrequencyTypes = FrequencyTypes.OneTime; schedule.ActiveStartDate = DateTime.Today; schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0); schedule.Create(); // Create Job job = new Job(jobServer, "Job_1"); job.Create(); job.AddSharedSchedule(schedule.ID); job.ApplyToTargetServer(server.Name); // Create JobStep step = new JobStep(job, "Step_1"); step.Command = "SELECT 1"; step.SubSystem = AgentSubSystem.TransactSql; step.Create(); }
public void CreateJob(WAMSQLJob wamJob, string server = "localhost") { Server srv = null; if (server == "localhost") { srv = ConnectToLocalDatabase(_username, _password); } else { srv = ConnectToRemoteDatabase(server, _username, _password); } if (srv != null) { try { Job job = new Job(srv.JobServer, wamJob.Name); job.Description = wamJob.Description; job.Create(); JobStep jobStep = new JobStep(job, wamJob.JobStepName); jobStep.Command = wamJob.JobStepCommand; jobStep.OnSuccessAction = StepCompletionAction.GoToStep; jobStep.OnSuccessStep = 2; jobStep.OnFailAction = StepCompletionAction.GoToStep; jobStep.OnFailStep = 3; jobStep.Create(); JobStep successStep = new JobStep(job, "ReportSuccess"); successStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Success',null)"; successStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess; successStep.OnFailAction = StepCompletionAction.GoToStep; successStep.OnFailStep = 3; successStep.Create(); JobStep failStep = new JobStep(job, "ReportFailure"); failStep.Command = $"INSERT INTO WAM.dbo.JobHistory VALUES(NEWID(),{wamJob.JobStepName},'SQL',GETDATE(),'Failure','Not Available'"; failStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess; failStep.OnFailAction = StepCompletionAction.QuitWithFailure; failStep.Create(); JobSchedule jobSched = new JobSchedule(job, wamJob.ScheduleName); jobSched.FrequencyTypes = wamJob.ScheduleFrequencyType; jobSched.FrequencyRecurrenceFactor = wamJob.ScheduleFrequencyRecurrenceFactor; jobSched.FrequencyInterval = wamJob.ScheduleFrequencyInterval; jobSched.ActiveStartTimeOfDay = wamJob.ScheduleActiveStartTimeOfDay; jobSched.Create(); } catch (Exception ex) { throw new Exception("Failed To Create Job: " + wamJob.Name + " - " + ex.Message); } } else { throw new Exception("Failed To Connect to SQL Job Server"); } }
public void CreateJob(WAMSQLJob wamJob, string username, string password, string server = "localhost") { Server srv = null; if (server == "localhost") { srv = ConnectToLocalDatabase(username, password); } else { srv = ConnectToRemoteDatabase(server, username, password); } if (srv != null) { try { Operator op = new Operator(srv.JobServer, "WAM_Operator"); op.NetSendAddress = "WAM_Server"; op.Create(); Job job = new Job(srv.JobServer, wamJob.Name); job.Description = wamJob.Description; job.Create(); JobStep jobStep = new JobStep(job, wamJob.JobStepName); jobStep.Command = wamJob.JobStepCommand; jobStep.OnFailAction = StepCompletionAction.QuitWithFailure; jobStep.Create(); JobSchedule jobSched = new JobSchedule(job, wamJob.ScheduleName); jobSched.FrequencyTypes = wamJob.ScheduleFrequencyType; jobSched.FrequencyRecurrenceFactor = wamJob.ScheduleFrequencyRecurrenceFactor; jobSched.FrequencyInterval = wamJob.ScheduleFrequencyInterval; jobSched.ActiveStartTimeOfDay = wamJob.ScheduleActiveStartTimeOfDay; jobSched.Create(); } catch (Exception ex) { throw new Exception("Failed To Create Job: " + wamJob.Name + " - " + ex.Message); } } else { throw new Exception("Failed To Connect to SQL Job Server"); } }
static void Main(string[] args) { Server server = new Server("localhost"); JobServer jobServer = server.JobServer; foreach (Job serverJob in jobServer.Jobs) { Console.WriteLine("Job: {0} - Owner: {1}", serverJob.Name, serverJob.OwnerLoginName); } Job job = new Job(jobServer, "Clean PO Table"); job.Create(); JobStep jobStep = new JobStep(job, "Delete old records"); jobStep.Command = "DELETE FROM dbo.PurchaseOrders WHERE OrderDateTime < DATEADD(DAY, -30, GETDATE())"; jobStep.DatabaseName = "SmoDemoDatabase"; jobStep.Create(); JobSchedule jobSchedule = new JobSchedule(job, "Nightly Run"); jobSchedule.ActiveStartTimeOfDay = new TimeSpan(3, 0, 0); jobSchedule.FrequencyInterval = 1; jobSchedule.FrequencyTypes = FrequencyTypes.Daily; jobSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once; jobSchedule.Create(); Console.WriteLine(); Console.WriteLine(); Console.WriteLine("Finished"); Console.ReadKey(); }
/// <summary> /// 创建包含步骤和计划的作业,返回 string jobid /// </summary> /// <param name="KeyId">对象ID</param> /// <returns>返回 string</returns> public string CreateBilling(string KeyId, string JobType, string KillJobID) { try { #region 定义变量 //数据库名称 string strDatabaseName = string.Empty; //作业名称 string strJobName = string.Empty; //作业ID string strJobID = string.Empty; //作业说明 string strDescription = string.Empty; //开始时间 DateTime dtStartDate = System.DateTime.Now; //计划频率,Daily:每天、Weekly:每周、Monthly:每月、Run Once:只运行一次 FrequencyTypes FrequencyType = FrequencyTypes.Daily; //开始运行时间 如:10:30:00 string strRunStartDate = string.Empty; //结束运行时间,如:11:05:00 string strRunEndDate = string.Empty; //数据库服务名 string strSQLServer = string.Empty; //数据库登录用户 string strUser = string.Empty; //数据库登录密码 string strPassword = string.Empty; //时 int hours = 0; //分 int minutes = 0; //秒 int seconds = 0; //命令行 string JobCommandString = string.Empty; vSchedulerEmailBackup vSchedulerEmailBackupObj = null; #endregion //返回vSchedulerEmailBackup 对象,根据id查询 vSchedulerEmailBackupObj = this.FindvSchedulerEmailBackupById(KeyId); if (vSchedulerEmailBackupObj != null) { #region 变量赋值 //strDatabaseName = "ServiceDirectDB"; strDatabaseName = this.GetConfig("strDatabaseName"); strJobName = vSchedulerEmailBackupObj.TaskName; if (vSchedulerEmailBackupObj.JobID != null) { strJobID = vSchedulerEmailBackupObj.JobID.ToString(); } //描述:保存的是KillJob的ID strDescription = KillJobID; dtStartDate = Convert.ToDateTime(vSchedulerEmailBackupObj.StartTime); switch (vSchedulerEmailBackupObj.ScheduleType) { case "Daily": FrequencyType = FrequencyTypes.Daily; break; case "Run Once": FrequencyType = FrequencyTypes.OneTime; break; } DateTime dtRunOnlyStart = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyStart); DateTime dtRunOnlyEnd = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd); strRunStartDate = dtRunOnlyStart.ToLongTimeString(); strRunEndDate = dtRunOnlyEnd.ToLongTimeString(); //开始运行时间 时:分:秒: hours = dtRunOnlyStart.Hour; minutes = dtRunOnlyStart.Minute; seconds = dtRunOnlyStart.Second; //获取自定义config的值 strSQLServer = this.GetConfig("strSQLServer"); strUser = this.GetConfig("strUser"); strPassword = this.GetConfig("strPassword"); #endregion #region 创建作业 ServiceDirectDBEntities objDB = new ServiceDirectDBEntities(); //获取数据库名 string strdatabase = objDB.Connection.Database; //获取数据库服务器名 string strDataSource = objDB.Connection.DataSource; ServerConnection conn = null; Server myServer = null; conn = new ServerConnection(strSQLServer, strUser, strPassword); myServer = new Server(conn); //调用删除Billing 的Job //DeleteJob(strJobID); Job jb = new Job(myServer.JobServer, strJobName); jb.Description = strDescription; //更改JOB状态 if (JobType == "enable") { jb.IsEnabled = true; } switch (JobType) { case "enable": jb.IsEnabled = true; break; case "disable": jb.IsEnabled = false; break; case "Insert": jb.IsEnabled = true; break; } //创建JOB jb.Create(); #endregion 创建作业 if (jb.JobID != null) { //获取命令行 JobCommandString = this.GetJobCommandString(vSchedulerEmailBackupObj, KeyId, jb); } #region 作业步骤 Steps steps = new Steps(); //创建步骤 steps.CreateStep(jb, strDatabaseName, JobCommandString, strRunEndDate); #endregion 作业步骤 #region 作业计划属性 JobSchedule jbsch = new JobSchedule(jb, "ScheduleBilling"); //计划频率,每几天一次 jbsch.FrequencyTypes = FrequencyType; if (vSchedulerEmailBackupObj.ScheduleType.Equals("Daily")) { //执行间隔 (天) jbsch.FrequencyInterval = 1; //当天只执行一次 jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Once; } if (strRunStartDate != string.Empty) { //开始执行时间 jbsch.ActiveStartTimeOfDay = new TimeSpan(hours, minutes, seconds); } //持续时间 if (dtStartDate != null) { //开始时间 jbsch.ActiveStartDate = dtStartDate; } else { jbsch.ActiveStartDate = DateTime.Now; } //创建SQL代理实例的作业调度 jbsch.Create(); jb.ApplyToTargetServer(myServer.JobServer.Name); //创建成功后立刻执行一次开始 //jb.Start(); //创建成功后立刻执行一次结束 #endregion 作业计划属性 //返回作业GUID return(jb.JobID.ToString()); } else { return(""); } } catch (Exception) { return(""); } }
private bool UpdateSQLJob(int nPeriod, bool bEnable, int nFreq, ref string err) { ServerConnection conn = null; try { string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLJob"].ConnectionString; System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connstring); conn = new ServerConnection(sqlconn); Server server = new Server(conn); // Get instance of SQL Agent SMO object JobServer jobServer = server.JobServer; Job job = jobServer.Jobs["EmailDispatch"]; if (job == null) { // Create Job job = new Job(jobServer, "EmailDispatch"); job.Create(); //job.AddSharedSchedule(schedule.ID); //job.ApplyToTargetServer(server.Name); } JobStep step = job.JobSteps["EmailDispatchProcess"]; if (step == null) { // Create JobStep step = new JobStep(job, "EmailDispatchProcess"); step.Command = "EXEC P_EmailDispatchForSchedule"; step.SubSystem = AgentSubSystem.TransactSql; step.DatabaseName = conn.DatabaseName; step.Create(); } JobSchedule schedule = job.JobSchedules["MinuteDispatch"]; if (schedule == null) { // Create a schedule schedule = new JobSchedule(job, "MinuteDispatch"); schedule.ActiveStartDate = DateTime.Today; schedule.FrequencyTypes = FrequencyTypes.Daily; schedule.FrequencyInterval = 1; if (nFreq == 0) schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour; else if(nFreq == 1) schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute; schedule.FrequencySubDayInterval = nPeriod; //schedule.ActiveStartTimeOfDay = new TimeSpan(DateTime.Now.Hour, (DateTime.Now.Minute + 2), 0); schedule.Create(); job.AddSharedSchedule(schedule.ID); job.ApplyToTargetServer("(local)"); } else { if (nFreq == 0) schedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour; else if (nFreq == 1) schedule.FrequencySubDayTypes = FrequencySubDayTypes.Minute; schedule.FrequencySubDayInterval = nPeriod; schedule.Alter(); } job.IsEnabled = bEnable; job.Alter(); conn.Disconnect(); return true; } catch (Exception ex) { err = ex.Message; if(conn != null) conn.Disconnect(); return false; } }
/// <summary> /// 创建包含步骤和计划的作业,返回 string jobid, Kill的JOB /// </summary> /// <param name="KeyId">对象ID</param> /// <returns>返回 string</returns> public string Create(string KeyId, string JobType) { try { #region 定义变量 //数据库名称 string strDatabaseName = string.Empty; //作业名称 string strJobName = string.Empty; //作业ID string strJobID = string.Empty; //作业说明 string strDescription = string.Empty; //开始时间 DateTime dtStartDate = System.DateTime.Now; //计划频率,Daily:每天、Weekly:每周、Monthly:每月、Run Once:只运行一次 FrequencyTypes FrequencyType = FrequencyTypes.Daily; //开始运行时间 如:10:30:00 string strRunStartDate = string.Empty; //结束运行时间,如:11:05:00 string strRunEndDate = string.Empty; //数据库服务名 string strSQLServer = string.Empty; //数据库登录用户 string strUser = string.Empty; //数据库登录密码 string strPassword = string.Empty; //时 int hours = 0; //分 int minutes = 0; //秒 int seconds = 0; //命令行 string JobCommandString = string.Empty; vSchedulerEmailBackup vSchedulerEmailBackupObj = null; #endregion //返回vSchedulerEmailBackup 对象,根据id查询 vSchedulerEmailBackupObj = this.FindvSchedulerEmailBackupById(KeyId); if (vSchedulerEmailBackupObj != null) { #region 变量赋值 //strDatabaseName = "ServiceDirectDB"; strDatabaseName = this.GetConfig("strDatabaseName"); string[] strJobNames = vSchedulerEmailBackupObj.TaskName.Split('-'); strJobName = strJobNames[0].ToString() + "-Kill"; if (vSchedulerEmailBackupObj.JobID != null) { strJobID = vSchedulerEmailBackupObj.JobID.ToString(); } strDescription = "KillUTP"; dtStartDate = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd); switch (vSchedulerEmailBackupObj.ScheduleType) { case "Daily": FrequencyType = FrequencyTypes.Daily; break; case "Run Once": FrequencyType = FrequencyTypes.OneTime; break; } DateTime dtRunOnlyStart = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyStart); DateTime dtRunOnlyEnd = Convert.ToDateTime(vSchedulerEmailBackupObj.RunOnlyEnd); strRunStartDate = dtRunOnlyStart.ToLongTimeString(); strRunEndDate = dtRunOnlyEnd.ToLongTimeString(); //开始运行时间 时:分:秒: hours = dtRunOnlyEnd.Hour; //结束时间减去5分钟之后在运行Kill的JOB minutes = dtRunOnlyEnd.Minute - 5; seconds = dtRunOnlyEnd.Second; //获取自定义config的值 strSQLServer = this.GetConfig("strSQLServer"); strUser = this.GetConfig("strUser"); strPassword = this.GetConfig("strPassword"); #endregion #region 创建作业 ServiceDirectDBEntities objDB = new ServiceDirectDBEntities(); //获取数据库名 string strdatabase = objDB.Connection.Database; //获取数据库服务器名 string strDataSource = objDB.Connection.DataSource; ServerConnection conn = null; Server myServer = null; conn = new ServerConnection(strSQLServer, strUser, strPassword); myServer = new Server(conn); string KillID = string.Empty; //调用删除job方法,传入job名称 //DeleteJob(KillID); DeleteJob(strJobID); Job jb = new Job(myServer.JobServer, strJobName); jb.Description = strDescription; //更改JOB状态 if (JobType == "enable") { jb.IsEnabled = true; } switch (JobType) { case "enable": jb.IsEnabled = true; break; case "disable": jb.IsEnabled = false; break; case "Insert": jb.IsEnabled = true; break; } //创建JOB jb.Create(); #endregion 创建作业 #region 作业步骤 Steps steps = new Steps(); //创建步骤 steps.CreateStepKill(jb, strDatabaseName, KeyId); #endregion 作业步骤 #region 作业计划属性 JobSchedule jbsch = new JobSchedule(jb, "ScheduleKill"); //计划频率,每几天一次 jbsch.FrequencyTypes = FrequencyType; if (vSchedulerEmailBackupObj.ScheduleType.Equals("Daily")) { //执行间隔 (天) jbsch.FrequencyInterval = 1; //当天只执行一次 jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Once; } if (strRunStartDate != string.Empty) { //开始执行时间 jbsch.ActiveStartTimeOfDay = new TimeSpan(hours, minutes, seconds); } //持续时间 if (dtStartDate != null) { //开始时间 jbsch.ActiveStartDate = dtStartDate; } else { jbsch.ActiveStartDate = DateTime.Now; } //创建SQL代理实例的作业调度 jbsch.Create(); jb.ApplyToTargetServer(myServer.JobServer.Name); //创建成功后立刻执行一次开始 //jb.Start(); //创建成功后立刻执行一次结束 #endregion 作业计划属性 string KillJobID = jb.JobID.ToString(); string BillinJobID = string.Empty; BillinJobID = this.CreateBilling(KeyId, JobType, KillJobID); //返回作业GUID return BillinJobID; } else { return ""; } } catch (Exception) { return ""; } }