public List <SelectListItem> getActions(string ServerName, Guid JobID, int JobStep) { List <SelectListItem> actions = new List <SelectListItem>(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); actions.Add(new SelectListItem { Text = "Go to the next step", Value = "GoToNextStep" }); actions.Add(new SelectListItem { Text = "Quit the job reporting success", Value = "QuitWithSuccess" }); actions.Add(new SelectListItem { Text = "Quit the job reporting failure", Value = "QuitWithFailure" }); Job job = dbServer.JobServer.GetJobByID(JobID); foreach (JobStep step in job.JobSteps) { if (step.ID != JobStep) { actions.Add(new SelectListItem { Text = "Go to step: [" + step.ID + "] " + step.Name, Value = "GoToStep:" + step.ID }); } } return(actions); }
public void deleteStep(string serverName, Guid jobID, int stepID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); job.JobSteps[stepID - 1].Drop(); job.JobSteps.Refresh(); }
public JobStepDetailsModel getStepDetails(string serverName, Guid jobID, int stepID) { JobStepDetailsModel step = new JobStepDetailsModel(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); JobStep jobstep = job.JobSteps[stepID - 1]; step.ServerName = serverName; step.JobID = jobID; step.StepNo = jobstep.ID; step.StepName = jobstep.Name; step.RunAs = jobstep.ProxyName; step.Database = jobstep.DatabaseName; step.Command = jobstep.Command; switch (jobstep.OnSuccessAction) { case StepCompletionAction.GoToNextStep: step.OnSuccess = "GoToNextStep"; break; case StepCompletionAction.QuitWithSuccess: step.OnSuccess = "QuitWithSuccess"; break; case StepCompletionAction.QuitWithFailure: step.OnSuccess = "QuitWithFailure"; break; case StepCompletionAction.GoToStep: step.OnSuccess = "GoToStep:" + jobstep.OnSuccessStep; break; } switch (jobstep.OnFailAction) { case StepCompletionAction.GoToNextStep: step.OnFailure = "GoToNextStep"; break; case StepCompletionAction.QuitWithSuccess: step.OnFailure = "QuitWithSuccess"; break; case StepCompletionAction.QuitWithFailure: step.OnFailure = "QuitWithFailure"; break; case StepCompletionAction.GoToStep: step.OnFailure = "GoToStep:" + jobstep.OnFailStep; break; } return(step); }
public void deleteSchedule(string serverName, Guid jobID, Guid scheduleUID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); JobSchedule schedule = job.JobSchedules[scheduleUID]; job.JobSchedules[scheduleUID].Drop(); job.JobSchedules.Refresh(); }
public List <JobHistorySummary> getHistory(string serverName, Guid jobID) { List <JobHistoryModel> stepHistory = new List <JobHistoryModel>(); List <JobHistorySummary> jobHistory = new List <JobHistorySummary>(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); JobHistoryFilter jhf = new JobHistoryFilter(); Job jb = dbServer.JobServer.GetJobByID(jobID); jhf.JobID = jb.JobID; DataTable dt = jb.EnumHistory(jhf); dt.DefaultView.Sort = "InstanceID ASC"; dt = dt.DefaultView.ToTable(); foreach (DataRow row in dt.Rows) { if (int.Parse(row["StepID"].ToString()) != 0) { stepHistory.Add(new JobHistoryModel { ServerName = serverName, JobID = jobID, JobName = row["JobName"].ToString(), StepID = int.Parse(row["StepID"].ToString()), ExecutionTime = DateTime.Parse(row["RunDate"].ToString()), Duration = TimeSpan.FromSeconds(double.Parse(row["RunDuration"].ToString())), Message = row["Message"].ToString(), StepName = row["StepName"].ToString() }); } else { jobHistory.Add(new JobHistorySummary { ServerName = serverName, JobID = jobID, JobName = row["JobName"].ToString(), StepID = int.Parse(row["StepID"].ToString()), ExecutionTime = DateTime.Parse(row["RunDate"].ToString()), Duration = TimeSpan.FromSeconds(double.Parse(row["RunDuration"].ToString())), Message = row["Message"].ToString(), StepName = row["StepName"].ToString(), JobHistories = stepHistory.OrderByDescending(i => i.StepID).ToList() }); stepHistory.Clear(); } } return(jobHistory); }
public void Add(string UserName, string ServerName, Guid JobID, string Action) { ConfigContext db = new ConfigContext(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); string JobName = dbServer.JobServer.GetJobByID(JobID).Name; db.Activity.Add(new ActivityLog { DateTime = DateTime.Now, UserName = UserName, ServerName = ServerName, JobName = JobName, Action = Action }); db.SaveChanges(); }
public void moveStepDown(string serverName, Guid jobID, int stepID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); JobStep newJobStep = job.JobSteps[stepID - 1]; StringCollection script = newJobStep.Script(); script[0] = script[0].Replace("@step_id=" + stepID, "@step_id=" + (stepID + 1)); job.JobSteps[stepID - 1].Drop(); dbServer.Refresh(); dbServer.ConnectionContext.ExecuteNonQuery(script); }
public List <SelectListItem> getDatabases(string ServerName) { List <SelectListItem> databases = new List <SelectListItem>(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); foreach (Database db in dbServer.Databases) { databases.Add(new SelectListItem { Text = db.Name, Value = db.Name }); } return(databases); }
public int addStep(string serverName, Guid jobID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); JobStep step = new JobStep(job, "New Step"); step.SubSystem = AgentSubSystem.TransactSql; step.OnSuccessAction = StepCompletionAction.QuitWithSuccess; step.OnFailAction = StepCompletionAction.QuitWithFailure; step.Create(); step.Refresh(); return(step.ID); }
public List <SelectListItem> getSteps(string ServerName, Guid JobID) { List <SelectListItem> steps = new List <SelectListItem>(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); Job job = dbServer.JobServer.GetJobByID(JobID); foreach (JobStep step in job.JobSteps) { steps.Add(new SelectListItem { Text = step.Name, Value = step.Name }); } return(steps); }
public void saveGeneral(JobDetailsModel job) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(job.ServerName); Job jobToUpdate = dbServer.JobServer.GetJobByID(job.JobID); if (jobToUpdate.Name != job.JobName) { jobToUpdate.Rename(job.JobName); } jobToUpdate.OwnerLoginName = job.Owner; jobToUpdate.Description = job.Description; jobToUpdate.IsEnabled = job.Enabled; jobToUpdate.StartStepID = job.StartStepID; jobToUpdate.Alter(); }
public List <SelectListItem> getProxies(string ServerName, AgentSubSystem StepType) { List <SelectListItem> proxies = new List <SelectListItem>(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); foreach (ProxyAccount proxy in dbServer.JobServer.ProxyAccounts) { DataTable dt = proxy.EnumSubSystems(); foreach (DataRow row in dt.Rows) { if (row["Name"].ToString() == StepType.ToString()) { proxies.Add(new SelectListItem { Text = proxy.Name, Value = proxy.ID.ToString() }); } } } return(proxies); }
public List <JobStepListModel> getSteps(string serverName, Guid jobID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); List <JobStepListModel> steplist = new List <JobStepListModel>(); foreach (JobStep step in job.JobSteps) { steplist.Add(new JobStepListModel { StepNo = step.ID, StepName = step.Name, StepType = step.SubSystem, OnSuccess = step.OnSuccessAction, OnSuccessStep = step.OnSuccessStep, OnFailure = step.OnFailAction, OnFailureStep = step.OnFailStep }); } return(steplist); }
public JobDetailsModel getGeneral(string ServerName, Guid JobID) { JobDetailsModel jobDetails = new JobDetailsModel(); ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(ServerName); var job = dbServer.JobServer.GetJobByID(JobID); jobDetails.JobID = JobID; jobDetails.JobName = job.Name; jobDetails.Enabled = job.IsEnabled; jobDetails.Description = job.Description; jobDetails.Owner = job.OwnerLoginName; jobDetails.Created = job.DateCreated; jobDetails.LastExecuted = job.LastRunDate; jobDetails.LastModified = job.DateLastModified; jobDetails.ServerName = ServerName; jobDetails.StartStepID = job.StartStepID; jobDetails.StepCount = job.JobSteps.Count; return(jobDetails); }
public List <JobSummaryModel> getJobs(string selectedServer = null) { ConfigContext db = new ConfigContext(); List <ServerConfig> servers = new List <ServerConfig>(); servers = db.ServerConfiguration.ToList(); List <EditableCategories> editableCategories = new List <EditableCategories>(); editableCategories = db.EditableCategories.ToList(); List <JobSummaryModel> joblist = new List <JobSummaryModel>(); foreach (var server in servers) { if (string.IsNullOrEmpty(selectedServer) || selectedServer == server.ServerName) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(server.ServerName); DataSet ds = dbServer.ConnectionContext.ExecuteWithResults("exec msdb.dbo.sp_help_job"); foreach (DataRow row in ds.Tables[0].Rows) { switch (row["current_execution_status"].ToString()) { case "1": jobStatus = "Executing:" + row["current_execution_step"]; break; case "2": jobStatus = "Waiting for Thread"; break; case "3": jobStatus = "Between Retries"; break; case "4": jobStatus = "Idle"; break; case "5": jobStatus = "Suspended"; break; case "6": jobStatus = "[Obsolete]"; break; case "7": jobStatus = "PerformingCompletionActions"; break; default: jobStatus = ""; break; } switch (row["last_run_outcome"].ToString()) { case "0": lastOutcome = "Failed"; break; case "1": lastOutcome = "Succeeded"; break; case "2": lastOutcome = "Retry"; break; case "3": lastOutcome = "Canceled"; break; default: lastOutcome = "Unknown"; break; } switch (row["last_run_date"].ToString()) { case "0": lastRun = DateTime.MinValue; break; default: lastRun = DateTime.ParseExact(row["last_run_date"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture) .Add(TimeSpan.ParseExact(row["last_run_time"].ToString().PadLeft(6, '0'), "hhmmss", CultureInfo.InvariantCulture)); break; } switch (row["next_run_date"].ToString()) { case "0": nextRun = DateTime.MinValue; break; default: nextRun = DateTime.ParseExact(row["next_run_date"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture) .Add(TimeSpan.ParseExact(row["next_run_time"].ToString().PadLeft(6, '0'), "hhmmss", CultureInfo.InvariantCulture)); break; } if (row["has_step"].ToString() != "0") { runable = true; } else { runable = false; } editable = true; string categoryName = row["category"].ToString(); EditableCategories editableCategory = new EditableCategories(); editableCategory = db.EditableCategories.FirstOrDefault(m => m.CategoryName == categoryName); if (editableCategory == null) { editableCategory = new EditableCategories() { CategoryName = "Unknown", Editable = true } } ; if (!editableCategory.Editable) { editable = false; } joblist.Add(new JobSummaryModel { JobID = Guid.Parse(row["job_id"].ToString()), ServerName = row["originating_server"].ToString(), JobName = row["name"].ToString(), Enabled = Convert.ToBoolean(row["enabled"]), Status = jobStatus, LastOutcome = lastOutcome, LastRun = lastRun, NextRun = nextRun, Category = row["category"].ToString(), Runable = runable, Scheduled = Convert.ToBoolean(row["has_schedule"]), Description = row["description"].ToString(), Owner = row["owner"].ToString(), Editable = editable }); } dbServer.ConnectionContext.Disconnect(); } } var sortedList = joblist.OrderBy(o => o.JobName).ToList(); return(sortedList); } }
public void saveStepDetails(JobStepDetailsModel Step) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(Step.ServerName); Job job = dbServer.JobServer.GetJobByID(Step.JobID); JobStep stepToUpdate = job.JobSteps[Step.StepNo - 1]; if (stepToUpdate.Name != Step.StepName) { stepToUpdate.Rename(Step.StepName); } stepToUpdate.DatabaseName = Step.Database; stepToUpdate.Command = Step.Command; switch (Step.OnSuccess) { case "GoToNextStep": stepToUpdate.OnSuccessAction = StepCompletionAction.GoToNextStep; stepToUpdate.OnSuccessStep = 0; break; case "QuitWithSuccess": stepToUpdate.OnSuccessAction = StepCompletionAction.QuitWithSuccess; stepToUpdate.OnSuccessStep = 0; break; case "QuitWithFailure": stepToUpdate.OnSuccessAction = StepCompletionAction.QuitWithFailure; stepToUpdate.OnSuccessStep = 0; break; default: stepToUpdate.OnSuccessAction = StepCompletionAction.GoToStep; stepToUpdate.OnSuccessStep = int.Parse(Step.OnSuccess.Split(':')[1]); break; } switch (Step.OnFailure) { case "GoToNextStep": stepToUpdate.OnFailAction = StepCompletionAction.GoToNextStep; stepToUpdate.OnFailStep = 0; break; case "QuitWithSuccess": stepToUpdate.OnFailAction = StepCompletionAction.QuitWithSuccess; stepToUpdate.OnFailStep = 0; break; case "QuitWithFailure": stepToUpdate.OnFailAction = StepCompletionAction.QuitWithFailure; stepToUpdate.OnFailStep = 0; break; default: stepToUpdate.OnFailAction = StepCompletionAction.GoToStep; stepToUpdate.OnFailStep = int.Parse(Step.OnFailure.Split(':')[1]); break; } stepToUpdate.Alter(); stepToUpdate.Refresh(); }
public JobScheduleDetailsModel getScheduleDetails(string serverName, Guid jobID, Guid scheduleUID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); JobScheduleDetailsModel schedule = new JobScheduleDetailsModel(); Job job = dbServer.JobServer.GetJobByID(jobID); var jobschedule = job.JobSchedules[scheduleUID]; schedule.IsEnabled = jobschedule.IsEnabled; schedule.Name = jobschedule.Name; schedule.ScheduleFrequency = jobschedule.FrequencyTypes.ToString(); schedule.ServerName = serverName; schedule.JobID = jobID; schedule.ScheduleUID = scheduleUID; schedule = setDefaults(schedule); switch (jobschedule.FrequencyTypes.ToString()) { case "OneTime": schedule.OneTimeStartDate = jobschedule.ActiveStartDate; schedule.OneTimeStartTimeOfDay = jobschedule.ActiveStartTimeOfDay; break; case "Daily": schedule.DailyRecursEvery = jobschedule.FrequencyInterval; break; case "Weekly": WeekDays days = (WeekDays)jobschedule.FrequencyInterval; schedule.WeeklyRecursEvery = jobschedule.FrequencyRecurrenceFactor; if (days.HasFlag(WeekDays.Sunday)) { schedule.WeeklySunday = true; } if (days.HasFlag(WeekDays.Monday)) { schedule.WeeklyMonday = true; } if (days.HasFlag(WeekDays.Tuesday)) { schedule.WeeklyTuesday = true; } if (days.HasFlag(WeekDays.Wednesday)) { schedule.WeeklyWednesday = true; } if (days.HasFlag(WeekDays.Thursday)) { schedule.WeeklyThursday = true; } if (days.HasFlag(WeekDays.Friday)) { schedule.WeeklyFriday = true; } if (days.HasFlag(WeekDays.Saturday)) { schedule.WeeklySaturday = true; } break; case "Monthly": schedule.MonthlyDayNo = jobschedule.FrequencyInterval; schedule.MonthlyFrequency = jobschedule.FrequencyRecurrenceFactor; break; case "MonthlyRelative": MonthlyRelativeWeekDays monthdays = (MonthlyRelativeWeekDays)jobschedule.FrequencyInterval; schedule.MonthlyRelativeFreq = jobschedule.FrequencyRecurrenceFactor; schedule.MonthlyRelativeFreqSubDayType = jobschedule.FrequencyRelativeIntervals.ToString(); if (monthdays.HasFlag(MonthlyRelativeWeekDays.Sunday)) { schedule.MonthlyRelativeSubFreq = "Sunday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Monday)) { schedule.MonthlyRelativeSubFreq = "Monday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Tuesday)) { schedule.MonthlyRelativeSubFreq = "Tuesday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Wednesday)) { schedule.MonthlyRelativeSubFreq = "Wednesday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Thursday)) { schedule.MonthlyRelativeSubFreq = "Thursday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Friday)) { schedule.MonthlyRelativeSubFreq = "Friday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.Saturday)) { schedule.MonthlyRelativeSubFreq = "Saturday"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.WeekDays)) { schedule.MonthlyRelativeSubFreq = "WeekDays"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.WeekEnds)) { schedule.MonthlyRelativeSubFreq = "WeekEnds"; } if (monthdays.HasFlag(MonthlyRelativeWeekDays.EveryDay)) { schedule.MonthlyRelativeSubFreq = "EveryDay"; } break; default: schedule.OneTimeStartDate = jobschedule.ActiveStartDate; schedule.OneTimeStartTimeOfDay = jobschedule.ActiveStartTimeOfDay; break; } if (jobschedule.ActiveEndTimeOfDay.Hours == 23 && jobschedule.ActiveEndTimeOfDay.Minutes == 59 && jobschedule.ActiveEndTimeOfDay.Seconds == 59) { schedule.DailyFreqOccursOnce = true; schedule.DailyFreqOccursOnceTime = jobschedule.ActiveStartTimeOfDay; } else { schedule.DailyFreqOccursOnce = false; schedule.DailyFreqOccursEvery = jobschedule.FrequencySubDayInterval; schedule.DailyFreqSubDay = jobschedule.FrequencySubDayTypes.ToString(); schedule.DailyFreqStartingTime = jobschedule.ActiveStartTimeOfDay; schedule.DailyFreqEndingTime = jobschedule.ActiveEndTimeOfDay; } schedule.DurationStartDate = jobschedule.ActiveStartDate; schedule.DurationEndDate = jobschedule.ActiveEndDate; if (jobschedule.ActiveEndDate == DateTime.MaxValue.Date) { schedule.DurationNoEndDate = true; } return(schedule); }
public void saveScheduleDetails(JobScheduleDetailsModel schedule) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(schedule.ServerName); Job job = dbServer.JobServer.GetJobByID(schedule.JobID); JobSchedule scheduleToUpdate = job.JobSchedules[schedule.ScheduleUID]; if (schedule.Name != scheduleToUpdate.Name) { scheduleToUpdate.Rename(schedule.Name); scheduleToUpdate.Refresh(); } scheduleToUpdate.IsEnabled = schedule.IsEnabled; switch (schedule.ScheduleFrequency) { case "OneTime": scheduleToUpdate.FrequencyTypes = FrequencyTypes.OneTime; scheduleToUpdate.ActiveStartDate = schedule.OneTimeStartDate; scheduleToUpdate.ActiveStartTimeOfDay = schedule.OneTimeStartTimeOfDay; break; case "Daily": scheduleToUpdate.FrequencyTypes = FrequencyTypes.Daily; scheduleToUpdate.FrequencyInterval = schedule.DailyRecursEvery; scheduleToUpdate = setCommon(scheduleToUpdate, schedule); break; case "Weekly": scheduleToUpdate.FrequencyTypes = FrequencyTypes.Weekly; scheduleToUpdate.FrequencyRecurrenceFactor = schedule.WeeklyRecursEvery; scheduleToUpdate.FrequencyInterval = 0; if (schedule.WeeklySunday) { scheduleToUpdate.FrequencyInterval = +1; } if (schedule.WeeklyMonday) { scheduleToUpdate.FrequencyInterval = +2; } if (schedule.WeeklyTuesday) { scheduleToUpdate.FrequencyInterval = +4; } if (schedule.WeeklyWednesday) { scheduleToUpdate.FrequencyInterval = +8; } if (schedule.WeeklyThursday) { scheduleToUpdate.FrequencyInterval = +16; } if (schedule.WeeklyFriday) { scheduleToUpdate.FrequencyInterval = +32; } if (schedule.WeeklySaturday) { scheduleToUpdate.FrequencyInterval = +64; } scheduleToUpdate = setCommon(scheduleToUpdate, schedule); break; case "Monthly": scheduleToUpdate.FrequencyTypes = FrequencyTypes.Monthly; scheduleToUpdate.FrequencyRecurrenceFactor = schedule.MonthlyFrequency; scheduleToUpdate.FrequencyInterval = schedule.MonthlyDayNo; scheduleToUpdate = setCommon(scheduleToUpdate, schedule); break; case "MonthlyRelative": scheduleToUpdate.FrequencyTypes = FrequencyTypes.MonthlyRelative; if (schedule.MonthlyRelativeSubFreq == "Sunday") { scheduleToUpdate.FrequencyInterval = 1; } if (schedule.MonthlyRelativeSubFreq == "Monday") { scheduleToUpdate.FrequencyInterval = 2; } if (schedule.MonthlyRelativeSubFreq == "Tuesday") { scheduleToUpdate.FrequencyInterval = 3; } if (schedule.MonthlyRelativeSubFreq == "Wednesday") { scheduleToUpdate.FrequencyInterval = 4; } if (schedule.MonthlyRelativeSubFreq == "Thursday") { scheduleToUpdate.FrequencyInterval = 5; } if (schedule.MonthlyRelativeSubFreq == "Friday") { scheduleToUpdate.FrequencyInterval = 6; } if (schedule.MonthlyRelativeSubFreq == "Saturday") { scheduleToUpdate.FrequencyInterval = 7; } if (schedule.MonthlyRelativeSubFreq == "Weekdays") { scheduleToUpdate.FrequencyInterval = 9; } if (schedule.MonthlyRelativeSubFreq == "WeekEnds") { scheduleToUpdate.FrequencyInterval = 10; } if (schedule.MonthlyRelativeSubFreq == "EveryDay") { scheduleToUpdate.FrequencyInterval = 8; } switch (schedule.MonthlyRelativeSubFreq) { case "First": scheduleToUpdate.FrequencyRelativeIntervals = FrequencyRelativeIntervals.First; break; case "Second": scheduleToUpdate.FrequencyRelativeIntervals = FrequencyRelativeIntervals.Second; break; case "Third": scheduleToUpdate.FrequencyRelativeIntervals = FrequencyRelativeIntervals.Third; break; case "Fourth": scheduleToUpdate.FrequencyRelativeIntervals = FrequencyRelativeIntervals.Fourth; break; case "Last": scheduleToUpdate.FrequencyRelativeIntervals = FrequencyRelativeIntervals.Last; break; } scheduleToUpdate.FrequencyRecurrenceFactor = schedule.MonthlyRelativeFreq; scheduleToUpdate = setCommon(scheduleToUpdate, schedule); break; case "AutoStart": scheduleToUpdate.FrequencyTypes = FrequencyTypes.AutoStart; scheduleToUpdate.FrequencyInterval = 0; scheduleToUpdate.FrequencyRecurrenceFactor = 0; scheduleToUpdate.FrequencyRelativeIntervals = 0; scheduleToUpdate.FrequencySubDayInterval = 0; scheduleToUpdate.FrequencySubDayTypes = FrequencySubDayTypes.Unknown; break; case "OnIdle": scheduleToUpdate.FrequencyTypes = FrequencyTypes.OneTime; scheduleToUpdate.FrequencyInterval = 0; scheduleToUpdate.FrequencyRecurrenceFactor = 0; scheduleToUpdate.FrequencyRelativeIntervals = 0; scheduleToUpdate.FrequencySubDayInterval = 0; scheduleToUpdate.FrequencySubDayTypes = FrequencySubDayTypes.Unknown; break; } scheduleToUpdate.Alter(); scheduleToUpdate.Refresh(); }
public List <JobScheduleListModel> getSchedules(string serverName, Guid jobID) { ConnectSqlServer connection = new ConnectSqlServer(); Server dbServer = connection.Connect(serverName); Job job = dbServer.JobServer.GetJobByID(jobID); List <JobScheduleListModel> schedulelist = new List <JobScheduleListModel>(); string sql = @"SELECT msdb.dbo.sysjobs.job_id AS [JobID] , msdb.dbo.sysjobschedules.schedule_id AS [ScheduleID] , msdb.dbo.sysschedules.schedule_uid AS [ScheduleUID] , msdb.dbo.sysschedules.name AS [ScheduleName] , CASE WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled' WHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime THEN 'Once on ' + CONVERT( CHAR(10) , CAST( CAST( msdb.dbo.sysschedules.active_start_date AS VARCHAR ) AS DATETIME ) , 102 -- yyyy.mm.dd ) WHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily THEN 'Daily' WHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Weekly on ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' weeks on ' END + LEFT( CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END , LEN( CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 -- LEN() ignores trailing spaces ) WHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CAST( msdb.dbo.sysschedules.freq_interval AS VARCHAR ) + CASE WHEN msdb.dbo.sysschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st' WHEN msdb.dbo.sysschedules.freq_interval IN ( 2, 22 ) THEN 'nd' WHEN msdb.dbo.sysschedules.freq_interval IN ( 3, 23 ) THEN 'rd' ELSE 'th' END WHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative THEN CASE WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1 THEN 'Every ' + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR ) + ' months on the ' END + CASE msdb.dbo.sysschedules.freq_relative_interval WHEN 0x01 THEN 'first ' WHEN 0x02 THEN 'second ' WHEN 0x04 THEN 'third ' WHEN 0x08 THEN 'fourth ' WHEN 0x10 THEN 'last ' END + CASE msdb.dbo.sysschedules.freq_interval WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'day' WHEN 9 THEN 'week day' WHEN 10 THEN 'weekend day' END WHEN msdb.dbo.sysschedules.freq_type = 0x40 THEN 'Automatically starts when SQLServerAgent starts.' WHEN msdb.dbo.sysschedules.freq_type = 0x80 THEN 'Starts whenever the CPUs become idle' ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysschedules.freq_subday_type = 0x1 OR msdb.dbo.sysschedules.freq_type = 0x1 THEN ' at ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100) + ' PM' end WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' every ' + CAST( msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR ) + CASE freq_subday_type WHEN 0x2 THEN ' second' WHEN 0x4 THEN ' minute' WHEN 0x8 THEN ' hour' END + CASE WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's' ELSE '' -- Added default 3/21/08; John Arnott END ELSE '' END + CASE WHEN msdb.dbo.sysjobs.enabled = 0 THEN '' WHEN msdb.dbo.sysjobs.job_id IS NULL THEN '' WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' between ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_start_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_start_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_start_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_start_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_start_time % 10000)/100)) + ' PM' end + ' and ' + Case -- Depends on time being integer to drop right-side digits when(msdb.dbo.sysschedules.active_end_time % 1000000)/10000 = 0 then '12' + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 10 then convert(char(1),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000 < 12 then convert(char(2),(msdb.dbo.sysschedules.active_end_time % 1000000)/10000) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' AM' when (msdb.dbo.sysschedules.active_end_time % 1000000)/10000< 22 then convert(char(1),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' PM' else convert(char(2),((msdb.dbo.sysschedules.active_end_time % 1000000)/10000) - 12) + ':' +Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100))) + rtrim(convert(char(2),(msdb.dbo.sysschedules.active_end_time % 10000)/100)) + ' PM' end ELSE '' END AS Schedule, CASE WHEN msdb.dbo.sysschedules.enabled = 1 THEN 'True' WHEN msdb.dbo.sysschedules.enabled = 0 THEN 'False' END AS [Schedule Enabled] FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id where msdb.dbo.sysjobs.job_id = '" + jobID + @"' order by 2 "; DataSet ds = dbServer.ConnectionContext.ExecuteWithResults(sql); foreach (DataRow row in ds.Tables[0].Rows) { schedulelist.Add(new JobScheduleListModel { ScheduleID = int.Parse(row["ScheduleID"].ToString()), ScheduleUID = Guid.Parse(row["ScheduleUID"].ToString()), Name = row["ScheduleName"].ToString(), Enabled = bool.Parse(row["Schedule Enabled"].ToString()), Description = row["Schedule"].ToString() }); } return(schedulelist); }