February 22, 2006 at 3:47 am
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.
February 22, 2006 at 6:55 am
exec
msdb.dbo.sp_help_job
exec
msdb.dbo.sp_help_schedule
exec
msdb.dbo.sp_help_jobschedule @job_name='YourJobname'
February 22, 2006 at 9:41 pm
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
February 23, 2006 at 7:25 am
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
February 23, 2006 at 10:39 pm
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)
February 24, 2006 at 7:11 am
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