I have what is a simple task. Using C# I want to schedule a SQL Server
Stored Procedure to run at a specified date & time.
I've seen plenty of examples of running SQL code like this but none about
running a stored procedure. The closest I've come is this:
http://msdn.microsoft.com/library/d... />
j_9uwk.asp
Can anyone point me to an example that would help me accomplish what I'm
trying to do?
Robert W.
Vancouver, BC
www.mwtech.comHi,
Below is an example I knocked together of calling SQLDMO from C#. It's
by no means perfect but it should be enough to get you started. You'll
probably need to clean up some formatting as the action of posting
probably ruined all of it. Just a few things to note:
1) You will need to reference the appropriate DLL for SQLDMO in your C#
project
2) You can add a "using SQLDMO;" statement to your code if you don't
want to prefix SQLDMO objects with "SQLDMO".
Hope this helps a bit
========================================
=======
public static void createNewJob()
{
/* Variable declaration */
SQLDMO.SQLServer theServer; //Object representing the connection to a
DB server
SQLDMO.Job jobToBeCreated; //Object representing the job to be created
on the server
SQLDMO.JobSchedule scheduleForCreatedJob; //Schedule of job to create
SQLDMO.JobStep stepForCreatedJob; //What the job will do
/*
* Process done in this order:
*
* 1) Connect to the database server
* 2) Create a Job object representing the job we want to add
* 3) Create a JobStep object representing what we want this job to do
(e.g. execute some T-SQL)
* 4) Create a JobSchedule object representing when we want this job
executed
* 5) Add the JobStep and JobSchedule objects to the Job object
* 6) Add the Job object to the jobs currently defined on the server
*
* NOTE: We need to connect to the server first because some methods
used to define a job require that
* we be connected to a database server
*/
theServer = new SQLDMO.SQLServerClass();
theServer.LoginSecure = false; //Use SQL Server Authentication
theServer.Login = "sa"; //Change to whatever SQL Server login you want
theServer.Password = ""; //Change to the password associated with the
user set previously
theServer.Connect("N-0U9LWEXW762ZV\\NATHAN", theServer.Login,
theServer.Password);
jobToBeCreated = new SQLDMO.JobClass();
jobToBeCreated.Name = "Example Job";
theServer.JobServer.Jobs.Add(jobToBeCreated);
stepForCreatedJob = new SQLDMO.JobStepClass();
stepForCreatedJob.Name = "Stored procedure execution";
stepForCreatedJob.StepID = 1;
stepForCreatedJob.Command = "exec sp_monitor"; //Change stored proc as
appropriate
scheduleForCreatedJob = new SQLDMO.JobScheduleClass();
scheduleForCreatedJob.Name = "Example schedule";
scheduleForCreatedJob.Enabled = true;
jobToBeCreated.JobSchedules.Add(scheduleForCreatedJob);
/*
* Configure a "one-shot" (i.e. the job will execute once {and only
once})
*/
scheduleForCreatedJob.Schedule.ActiveStartDate = 20060513; //Equivalent
to 05/13/2006 in dd/mm/yyyy
scheduleForCreatedJob.Schedule.ActiveStartTimeOfDay = 233000;
//Equivalent to hh:mm:ss
scheduleForCreatedJob.Schedule.ActiveEndDate = 20070513; //Value
irrelevant as the job is "one-shot"
scheduleForCreatedJob.Schedule.ActiveEndTimeOfDay = 230000; //Value
irrelevant as the job is "one-shot"
scheduleForCreatedJob.Schedule.FrequencyType =
SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_OneTime; //One shot
jobToBeCreated.JobSteps.Add(stepForCreatedJob);
jobToBeCreated.StartStepID = 1; //Step ID for the step we defined
previously
jobToBeCreated.ApplyToTargetServer("(local)"); //Job runs on server
running the SQL Server Agent
theServer.DisConnect();
}|||Nate,
Thank you so much! I will review your code and see if I can adapt it my
specific needs!!
Robert W.
Vancouver, BC
www.mwtech.com
"nate.vu@.gmail.com" wrote:
> Hi,
> Below is an example I knocked together of calling SQLDMO from C#. It's
> by no means perfect but it should be enough to get you started. You'll
> probably need to clean up some formatting as the action of posting
> probably ruined all of it. Just a few things to note:
> 1) You will need to reference the appropriate DLL for SQLDMO in your C#
> project
> 2) You can add a "using SQLDMO;" statement to your code if you don't
> want to prefix SQLDMO objects with "SQLDMO".
> Hope this helps a bit
> ========================================
=======
> public static void createNewJob()
> {
> /* Variable declaration */
> SQLDMO.SQLServer theServer; //Object representing the connection to a
> DB server
> SQLDMO.Job jobToBeCreated; //Object representing the job to be created
> on the server
> SQLDMO.JobSchedule scheduleForCreatedJob; //Schedule of job to create
> SQLDMO.JobStep stepForCreatedJob; //What the job will do
> /*
> * Process done in this order:
> *
> * 1) Connect to the database server
> * 2) Create a Job object representing the job we want to add
> * 3) Create a JobStep object representing what we want this job to do
> (e.g. execute some T-SQL)
> * 4) Create a JobSchedule object representing when we want this job
> executed
> * 5) Add the JobStep and JobSchedule objects to the Job object
> * 6) Add the Job object to the jobs currently defined on the server
> *
> * NOTE: We need to connect to the server first because some methods
> used to define a job require that
> * we be connected to a database server
> */
> theServer = new SQLDMO.SQLServerClass();
> theServer.LoginSecure = false; //Use SQL Server Authentication
> theServer.Login = "sa"; //Change to whatever SQL Server login you want
> theServer.Password = ""; //Change to the password associated with the
> user set previously
> theServer.Connect("N-0U9LWEXW762ZV\\NATHAN", theServer.Login,
> theServer.Password);
> jobToBeCreated = new SQLDMO.JobClass();
> jobToBeCreated.Name = "Example Job";
> theServer.JobServer.Jobs.Add(jobToBeCreated);
> stepForCreatedJob = new SQLDMO.JobStepClass();
> stepForCreatedJob.Name = "Stored procedure execution";
> stepForCreatedJob.StepID = 1;
> stepForCreatedJob.Command = "exec sp_monitor"; //Change stored proc as
> appropriate
> scheduleForCreatedJob = new SQLDMO.JobScheduleClass();
> scheduleForCreatedJob.Name = "Example schedule";
> scheduleForCreatedJob.Enabled = true;
> jobToBeCreated.JobSchedules.Add(scheduleForCreatedJob);
> /*
> * Configure a "one-shot" (i.e. the job will execute once {and only
> once})
> */
> scheduleForCreatedJob.Schedule.ActiveStartDate = 20060513; //Equivalent
> to 05/13/2006 in dd/mm/yyyy
> scheduleForCreatedJob.Schedule.ActiveStartTimeOfDay = 233000;
> //Equivalent to hh:mm:ss
> scheduleForCreatedJob.Schedule.ActiveEndDate = 20070513; //Value
> irrelevant as the job is "one-shot"
> scheduleForCreatedJob.Schedule.ActiveEndTimeOfDay = 230000; //Value
> irrelevant as the job is "one-shot"
> scheduleForCreatedJob.Schedule.FrequencyType =
> SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_OneTime; //One shot
> jobToBeCreated.JobSteps.Add(stepForCreatedJob);
> jobToBeCreated.StartStepID = 1; //Step ID for the step we defined
> previously
> jobToBeCreated.ApplyToTargetServer("(local)"); //Job runs on server
> running the SQL Server Agent
> theServer.DisConnect();
> }
>
