February 17, 2009 at 12:21 am
I want to create Jobs Dynamically using a stored procedure and also kick off those jobs.
Also we can delete those jobs once the execution is complete.
How can we do it in SQL Server. Any pointers please.
February 17, 2009 at 1:40 am
Taken from Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3b76545e-ad7d-4a05-8766-272546aeed2e.htm
How to: Create a SQL Server Agent Job (Transact-SQL)
This topic describes how to use stored procedures to create a Microsoft SQL Server Agent job.
To create a SQL Server Agent job
Execute sp_add_job to create a job.
Execute sp_add_jobstep to create one or more job steps.
Execute sp_add_schedule to create a schedule.
Execute sp_attach_schedule to attach a schedule to the job.
Execute sp_add_jobserver to set the server for the job.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
--Ramesh
February 17, 2009 at 4:41 am
Thanks Ramesh.
Can you point me to any sample code that creates a Job, kicks it off and then delete the job once the task is done?
February 17, 2009 at 5:24 am
raden (2/17/2009)
Thanks Ramesh.Can you point me to any sample code that creates a Job, kicks it off and then delete the job once the task is done?
Actually, I never had to do such task, so I really don't have any samples of it, may be if you can Google it, you will have something to start with.
--Ramesh
February 17, 2009 at 5:40 am
raden (2/17/2009)
Thanks Ramesh.Can you point me to any sample code that creates a Job, kicks it off and then delete the job once the task is done?
Just manually create the job in SSMS, there's an option somewhere in there to autodelete the job after it's run.
Then hit the script button and you'll be done with it.
February 17, 2009 at 5:43 am
... It's in the notifications tab, roughly translated from frech it should read automatically delete the job after ... (pick event from combo).
February 17, 2009 at 10:06 am
Ramesh (2/17/2009)
Taken from Books Online:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3b76545e-ad7d-4a05-8766-272546aeed2e.htm
How to: Create a SQL Server Agent Job (Transact-SQL)
This topic describes how to use stored procedures to create a Microsoft SQL Server Agent job.
To create a SQL Server Agent job
Execute sp_add_job to create a job.
Execute sp_add_jobstep to create one or more job steps.
Execute sp_add_schedule to create a schedule.
Execute sp_attach_schedule to attach a schedule to the job.
Execute sp_add_jobserver to set the server for the job.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
This is for SQLserver 2008. Are there equivalent procedures for SS2K5?
Plus I'd also like an equivalent for sp_start_job.
Derek
February 18, 2009 at 6:19 am
Derek Dongray (2/17/2009)
This is for SQLserver 2008. Are there equivalent procedures for SS2K5?Plus I'd also like an equivalent for sp_start_job.
I've taken the excerpt from SQL 2005 BOL, and are working as well. So, they are for SQL Server 2005+
--Ramesh
February 23, 2009 at 4:52 am
Ramesh (2/18/2009)
Derek Dongray (2/17/2009)
This is for SQLserver 2008. Are there equivalent procedures for SS2K5?Plus I'd also like an equivalent for sp_start_job.
I've taken the excerpt from SQL 2005 BOL, and are working as well. So, they are for SQL Server 2005+
They aren't present on my system (Developer Edition, SS2K5 SP3)/*------------------------
print @@version
exec sp_add_job
------------------------*/
Microsoft SQL Server 2005 - 9.00.4028.00 (Intel X86)
Oct 20 2008 19:45:04
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'sp_add_job'.
Is there something special I need to do to get them installed?
Edit: OK, so BOL isn't clear that they are in MSDB instead of MASTER so need to be specified as, for example, msdb.dbo.sp_add_job.
My mistake.
Derek
February 24, 2009 at 5:12 am
If you download SQL Server FineBuild from Codeplex, you will find the spSetDBMaintenance stored procedure creates database backup jobs for all databases that do not already have a backup.
The jobs created all run according to a schedule, but others have said how to start a job using sp_start_job, and how to get the job to automatically delete itself after it has run.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2016 at 3:06 am
May someone need it:
create procedure test (@variable varchar (40)) as
-- create dynamiclly command which job will execute. This allow you to pass parameter to job 🙂
declare @command varchar (300)='exec database.dbo.test '+@variable
-- create job
exec msdb..sp_add_job
@job_name =@variable,
@enabled=1,
@start_step_id=1,
@delete_level=1 -- means job will delete itself after succes. If set to 3, will delete always
-- code below allow you to pass any parameters wanted.
exec msdb..sp_add_jobstep
@job_name=@variable,
@step_id=1,
@step_name='exec',
@command=@command
exec msdb..sp_add_jobserver
@job_name = @variable,
@server_name = 'yourserver'
exec msdb..sp_start_job
@job_name=@variable
Best regards!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply