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);
}
