Michiel Wories' WebLog

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.

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

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.

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

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

       }