SMO Sample: Create and Start a Job

re: SMO Sample: Create and Start a Job

Xonnie Fay — Sun, 29 Jan 2006 00:39:01 GMT

Thank you. You code was for me big simplify and it was propably only one I found. Once again thanks. I didn't know how to get to earlier created job in SQL and run it.

re: SMO Sample: Create and Start a Job

george — Mon, 26 Nov 2007 15:38:47 GMT

ur blog was a lifesaver!!!was stuck with this problem for quite a while!!

re: SMO Sample: Create and Start a Job

Muthu — Thu, 03 Jan 2008 08:36:31 GMT

Hi,

Your sample is so simple to understand.

Can you please tell me how to start the jobs that are already created in sql server.

And other thing is,

The jobs is started already and it has failed in a particular step. In this situation how can I start the job from this particular step.

Please help me in this scenerios.

Thanks.

re: SMO Sample: Create and Start a Job

Egor — Fri, 29 Feb 2008 18:32:09 GMT

Hi! Thanks for your script, but it's not work for me with error

Start failed for Job 'New Smo Job' because in the job instance has not contained any steps...

And it's work fine:

Server sqlServer = new Server(strServerName);

JobServer jobServer = sqlServer.JobServer;

if (jobServer.Jobs.Contains("New Smo Job"))

{

jobServer.Jobs["New Smo Job"].Drop();

}

Job j = new Job(jobServer, "New Smo Job");

j.Create();

JobStep js = new JobStep(j, "Step 1");

js.SubSystem = AgentSubSystem.TransactSql;

js.Command = "select 1";

js.Create();

j.ApplyToTargetServer(sqlServer.Name);

j.Start();

re: SMO Sample: Create and Start a Job

THaouet — Fri, 16 May 2008 11:50:03 GMT

I used your code to create a job but I add a shedule to start it frequently like this:

public void CreateJob_Sql_Periodique(string JName, string dbName, string dName, travail t, DateTime Date, TimeSpan Heure, FrequencyTypes F, int i)

{

//création d'un job

Job SQLJob = new Job(MonServeur.JobServer, JName);

//pas besoin de connecter au serveur

//MonServeur.ConnectionContext.Connect();

//définir les propriétés du job

SQLJob.Description = "Chek And Back Up " + dbName;

SQLJob.Category = "Database Maintenance";

SQLJob.UserData = t;// le nom de la base de donnée et le dossier de sauvegarde

//créer le job sur le serveur Sql Agent

SQLJob.Create();

//définir les jobsteps du Job

JobStep aJobStep = new JobStep(SQLJob, "Step 1: Backup the Database");

//le type de la commande

aJobStep.SubSystem = AgentSubSystem.TransactSql;

//chaîne de caractère de la commande

string sExt = "EXEC master.dbo.xp_sqlmaint '-S " + DataSource + " -U " + User + " -P " + Password + " -D " + dbName + " -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB " + dName + " -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt " + dName + @"\" + "BackDB_Checks.txt'";

aJobStep.Command = sExt;

aJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;

aJobStep.OnFailAction = StepCompletionAction.QuitWithFailure;

//créer le Jobstep

aJobStep.Create();

//création d'une planification

JobSchedule SQLSchedule = new JobSchedule(SQLJob, "BackupA_" + dbName);

SQLSchedule.FrequencyTypes = F;

if (F == FrequencyTypes.Daily)

{

SQLSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Hour;

SQLSchedule.FrequencySubDayInterval = 1;

SQLSchedule.FrequencyInterval = i;

}

else

if (F == FrequencyTypes.Weekly)

{

SQLSchedule.FrequencyInterval = 2;

SQLSchedule.FrequencyRecurrenceFactor = i;

}

// pour hebdomadaire ou mensuel

//SQLSchedule.FrequencyRecurrenceFactor = r;

SQLSchedule.ActiveStartDate = Date;

SQLSchedule.ActiveStartTimeOfDay = Heure;

// this schedule has no end time or end date

SQLSchedule.ActiveEndDate = new DateTime(9999, 12, 30);

SQLSchedule.ActiveEndTimeOfDay = new TimeSpan(23, 59, 0);

SQLJob.JobSchedules.Refresh();

SQLSchedule.Create();

//appliquer les modifications au serveur

SQLJob.ApplyToTargetServer(DataSource);

SQLJob.Refresh();

MessageBox.Show("Planification de sauvegarde crée avec succes");

MessageBox.Show(sExt);

}