Info of Jobs & Schedules

  • Is there any System stored procedures or tables which gives me the information of the Jobs and schedules created for a Database.

    I know that we can see this information in Managament Studio but i want to see it through Stored Procedures.

  • exec

    msdb.dbo.sp_help_job

    exec

    msdb.dbo.sp_help_schedule

    exec

    msdb.dbo.sp_help_jobschedule @job_name='YourJobname'

  • Actually i want to write a code that will delete a job if it already exists,

    Now i can use EXEC sp_delete_job to delete the job and EXEC sp_help_job to check the existence of the job, now the confusion is EXEC sp_help_job gives me the result set not a flag so i want to know how can i link up these two procedures,

    Can some one suggests me the code for this prupose

  • DECLARE

    @JobName sysname

    SET

    @JobName=N'YourJobName'

    IF

    EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@JobName)

    BEGIN

    EXEC msdb.dbo.sp_delete_job @job_name=@JobName

    END

    SELECT

    * FROM msdb.dbo.sysjobs WHERE name=@JobName

  • I have to use following four tables, but 2 are working fine, one is giving me error and one is not giving me the result........can some one suggest me the solution

    /***********************************************/

    Following 2 are working fine

    SELECT * FROM msdb.dbo.sysjobs WHERE name='MyJob' 

    SELECT * FROM msdb.dbo.sysjobsteps WHERE step_name='MyStep'

    /***********************************************/

    Following is giving error "Invalid column name 'name'"

    SELECT

    * FROM msdb.dbo.sysjobschedules WHERE name='MySchedule'

    i have checked the columns of "msdb.dbo.sysjobschedules" and column [name] exists there

    /***********************************************/

    Following is not giving me the result

    SELECT * FROM msdb.dbo.systargetservers where server_name = 'MyServer'

    i have created 'MyServer' server and associated it with 'MyJob' now i dont know why the above table is not showing it (but it can be seen through sp_help_job)

  • 1) 

    /***********************************************/

    Following is giving error "Invalid column name 'name'"

    SELECT * FROM msdb.dbo.sysjobschedules WHERE name='MySchedule'

    i have checked the columns of "msdb.dbo.sysjobschedules" and column [name] exists there

    The job schedule schema in sql 2005 is different than 2000. Now it has a lookup(definition) table for all schedules (msdb.dbo.sysschedules). The table msdb.dbo.sysjobschedules just has a link (scheduel_id) to the schedule table.

    YOu need to join the two tables together:

    SELECT * FROM msdb.dbo.sysjobschedules A, msdb.dbo.sysschedules B WHERE A.schedule_id=B.schedule_id and B.name='MySchedule'

    2)

    /***********************************************/

    Following is not giving me the result

    SELECT * FROM msdb.dbo.systargetservers where server_name = 'MyServer'

    i have created 'MyServer' server and associated it with 'MyJob' now i dont know why the above table is not showing it (but it can be seen through sp_help_job)

    The target server table is for multi server jobs. If your job is on local server, the server id will be 0 in sysjobs table.

    SELECT * FROM msdb.dbo.sysjobs WHERE originating_server_id=0

     

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply