how to get the status of a sql job

Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advanceYou can use SP_HELP_JOB and a user who is not a member of the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns.

Execute permissions default to the public role in the msdb database.

How to get job history/error info. from a custom .exe being run by the job?

How to get job history/error info. from a custom .exe being run by the job?

We're using SQL Server 2005 and SQL Server Agent to run some jobs. These jobs execute a custom .exe program (written in C#).

What we're wondering is, is there any way to send status/history information from the custom .exe back to the job that kicked it off?

We're trying to provide execution info. to show if the job succeeded or if some error occurred in the .exe (thus the job didn't complete successfully).

Thanks!

Have the custom app logs to a file then when the job exits, load the logs/histories into a sql table.

How to get from JobID back to SubscriptionID

Hi!
I am hoping someone can explain how I can determine the subscription
associated with a currently running job (report). If a report is misbehaving,
we would like to determine the subscription that initiated the report
running. We can cancel the job to stop the report from running this time, but
we would like to be able to prevent it from running again.
I believe that this would mean tieing the jobid back to a subscriptionid,
but I have not been able to determine a way either programatically or via a
direct query of the RS tables to determine this.
Any help would be greatly appreciated.
Thanks in advance,
BobHello,
This is what I do:
--This will help you identify the OID of the report
SELECT ItemID, Path
FROM Catalog
--This will show all subscriptions for the report
SELECT *
FROM Subscriptions
WHERE Report_OID = '[ItemID from previous query]'
--This will tell you the job id
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
You can put all together, but I prefer to run them one by one because I have
a lot of reports and also several subscriptions per report.
Hope this helps.
Ricardo.
"bobhug" wrote:
> Hi!
> I am hoping someone can explain how I can determine the subscription
> associated with a currently running job (report). If a report is misbehaving,
> we would like to determine the subscription that initiated the report
> running. We can cancel the job to stop the report from running this time, but
> we would like to be able to prevent it from running again.
> I believe that this would mean tieing the jobid back to a subscriptionid,
> but I have not been able to determine a way either programatically or via a
> direct query of the RS tables to determine this.
> Any help would be greatly appreciated.
> Thanks in advance,
> Bob|||Ricardo,
Thanks for the response.
I do not see how this gets me to JobID however.
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
This would give me scheduleid and reportid, but I still do not know how to
get to jobid.
Perhaps I am just missing something.
Thanks,
Bob
"Ricardo Sampei" wrote:
> Hello,
> This is what I do:
> --This will help you identify the OID of the report
> SELECT ItemID, Path
> FROM Catalog
> --This will show all subscriptions for the report
> SELECT *
> FROM Subscriptions
> WHERE Report_OID = '[ItemID from previous query]'
> --This will tell you the job id
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> You can put all together, but I prefer to run them one by one because I have
> a lot of reports and also several subscriptions per report.
> Hope this helps.
> Ricardo.
> "bobhug" wrote:
> > Hi!
> > I am hoping someone can explain how I can determine the subscription
> > associated with a currently running job (report). If a report is misbehaving,
> > we would like to determine the subscription that initiated the report
> > running. We can cancel the job to stop the report from running this time, but
> > we would like to be able to prevent it from running again.
> > I believe that this would mean tieing the jobid back to a subscriptionid,
> > but I have not been able to determine a way either programatically or via a
> > direct query of the RS tables to determine this.
> > Any help would be greatly appreciated.
> >
> > Thanks in advance,
> > Bob|||Hello Bob,
Probably you have already figured out thi one, but anyway, here is the
answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
Server Agent.
Ricardo.
"bobhug" wrote:
> Ricardo,
> Thanks for the response.
> I do not see how this gets me to JobID however.
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> This would give me scheduleid and reportid, but I still do not know how to
> get to jobid.
> Perhaps I am just missing something.
> Thanks,
> Bob
> "Ricardo Sampei" wrote:
> > Hello,
> >
> > This is what I do:
> > --This will help you identify the OID of the report
> > SELECT ItemID, Path
> > FROM Catalog
> >
> > --This will show all subscriptions for the report
> > SELECT *
> > FROM Subscriptions
> > WHERE Report_OID = '[ItemID from previous query]'
> >
> > --This will tell you the job id
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> >
> > You can put all together, but I prefer to run them one by one because I have
> > a lot of reports and also several subscriptions per report.
> >
> > Hope this helps.
> >
> > Ricardo.
> >
> > "bobhug" wrote:
> >
> > > Hi!
> > > I am hoping someone can explain how I can determine the subscription
> > > associated with a currently running job (report). If a report is misbehaving,
> > > we would like to determine the subscription that initiated the report
> > > running. We can cancel the job to stop the report from running this time, but
> > > we would like to be able to prevent it from running again.
> > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > but I have not been able to determine a way either programatically or via a
> > > direct query of the RS tables to determine this.
> > > Any help would be greatly appreciated.
> > >
> > > Thanks in advance,
> > > Bob|||Ricardo,
Hi!
Now I understand the confusion.
The JobID I was referring to is the one as found in the RunningJobs table
and the Job class: http://tinyurl.com/9zyb2 , which is different than what
you are talking about, the name of the SQL Server agent job.
Thanks for responding,
Bob
"Ricardo Sampei" wrote:
> Hello Bob,
> Probably you have already figured out thi one, but anyway, here is the
> answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
> Server Agent.
> Ricardo.
> "bobhug" wrote:
> > Ricardo,
> >
> > Thanks for the response.
> > I do not see how this gets me to JobID however.
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > This would give me scheduleid and reportid, but I still do not know how to
> > get to jobid.
> >
> > Perhaps I am just missing something.
> > Thanks,
> > Bob
> >
> > "Ricardo Sampei" wrote:
> >
> > > Hello,
> > >
> > > This is what I do:
> > > --This will help you identify the OID of the report
> > > SELECT ItemID, Path
> > > FROM Catalog
> > >
> > > --This will show all subscriptions for the report
> > > SELECT *
> > > FROM Subscriptions
> > > WHERE Report_OID = '[ItemID from previous query]'
> > >
> > > --This will tell you the job id
> > > SELECT *
> > > FROM ReportSchedule
> > > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > >
> > > You can put all together, but I prefer to run them one by one because I have
> > > a lot of reports and also several subscriptions per report.
> > >
> > > Hope this helps.
> > >
> > > Ricardo.
> > >
> > > "bobhug" wrote:
> > >
> > > > Hi!
> > > > I am hoping someone can explain how I can determine the subscription
> > > > associated with a currently running job (report). If a report is misbehaving,
> > > > we would like to determine the subscription that initiated the report
> > > > running. We can cancel the job to stop the report from running this time, but
> > > > we would like to be able to prevent it from running again.
> > > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > > but I have not been able to determine a way either programatically or via a
> > > > direct query of the RS tables to determine this.
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks in advance,
> > > > Bob

How to get DMO to schedule a Stored Procedure as a Job?

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