February 4, 2016 at 1:01 pm
Hello
i need your help
they ask me to run in series about 10 jobs from sql server agent ( with daily execution time)
i will appreciate a way to do it
thanks
February 4, 2016 at 1:07 pm
If they need to run sequentially, create a single job with 10 jobsteps.
If they need to run at the same time, create 10 jobs.
February 4, 2016 at 1:10 pm
In series:
Job 1
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 2'
Job 2
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 3'
Job 3
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 4'
And so forth.
If you want them to run at the same time, create a single job with a single step that calls sp_start_job as many times as needed.
Each of the jobs will contain a history that can be queried as needed.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 4, 2016 at 1:17 pm
thanks
i want in series but i need probably to create an new job in sql server agent in order to run it
so can i create an new job called 'main' then i put there
Job 1
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 2'
Job 2
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 3'
Job 3
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 4'
February 4, 2016 at 1:21 pm
georgheretis (2/4/2016)
thanksi want in series but i need probably to create an new job in sql server agent in order to run it
so can i create an new job called 'main' then i put there
Job 1
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 2'
Job 2
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 3'
Job 3
Step 1 = Run the code
Step 2 = EXEC msdb..sp_start_job @job_name = 'Job 4'
No, if you want to follow the "in a seties" pattern suggested by Michael, then do it as he suggested. No extra "main". Just have the 10 jobs. Each of the first 9 jobs has two steps: one for the actual work, the second to start the next job. The tenth has no next job, so no second step. Then, put the first on a schedule and ensure that all other 9 are active but have no schedule. Now they will only start when told to - which is what the second step in each job does.
February 5, 2016 at 1:18 am
goodmorning
they change their mind and they want to run sequentially the 10 jobs, so i have to create a single job with 10 jobsteps.
so I AM going to create 1 main job with 10 jobsteps (All the jobs are existing in SQL Server agent)
my question is, lets assume that the 2nd job exists already in SQL Server agent and this have 3 steps.
how can i create the 2nd jobstep and to call the specific job without writing 3 more steps
so i want this
jobsteps1
jobsteps2
jobsteps3
and not like this
jobsteps1
jobsteps2_1
jobsteps2_2
jobsteps2_3
jobsteps3
if it is possible tell me in ;edit' selection what 'type' do i have to choose in order to call the 2nd step
thanks in advance
February 5, 2016 at 2:27 am
If you already have ten jobs in Agent that work fine, and now you want to make them run sequentially, then use what Michael posted and what I explained:
* First job goes on a schedule. Add one extra step (at the end - so it is step 2 if it had one step, but if it had 55 steps the step you add will be 56) to start the second job.
* Second to ninth job do not have a schedule. They each also get the extra step to kick off the next job.
* Tenth (last) job does not have a schedule and does not get an extra step.
If you do not have any existing jobs yet, then you could also consider creating a single job that has all the required actions in a single, long list of jobsteps (as I proposed in my first reply). But if you already have the job, then this will just introduce a lot of extra work for you.
February 5, 2016 at 2:32 am
sp_start_job does exactly what the name suggests: it starts the job, but doesn't wait for it to finish.
In this case, if you want to have a separate job to execute (start and wait for completion) other jobs in sequence, you will have to add some code to wait for the job completion.
This is the script I am using:
-- You don't have a TOOLS database? Create one, you'll thank me later.
USE [TOOLS]
GO
/****** Object: StoredProcedure [agent].[dba_wait_job_completion] Script Date: 05/02/16 10:35:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
* Waits for a SQLAgent job to complete.
* PARAMS:
* @name - Name of the job to wait for
* @category - Name of the job category to wait for
* @current_job_name - Name of the job.
* @timeout - Maximum number of minutes to wait for. 0 means no timeout.
*
* PERMISSIONS:
* -- msdb
* GRANT SELECT ON dbo.sysjobs TO <username,,sysname>
* -- master
* GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO <username,,sysname>
*/
CREATE PROCEDURE [dba_wait_job_completion]
@name SYSNAME = NULL,
@category SYSNAME = NULL,
@current_job_name SYSNAME = NULL,
@timeout INT = 0
AS
BEGIN
DECLARE @jobs_running INT
DECLARE @rnd INT
DECLARE @DELAY_STR CHAR(9)
DECLARE @END_TIME DATETIME
SET NOCOUNT ON
IF @timeout = 0
SET @END_TIME = DATEADD(year,10,GETDATE())
ELSE
SET @END_TIME = DATEADD(minute,@timeout,GETDATE())
CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id SYSNAME NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
WHILE GETDATE() < @END_TIME
BEGIN
SET @jobs_running = 0
SET @rnd = (SELECT CAST(RAND() * 60 AS INT))
SET @DELAY_STR = '000:00:' + RIGHT('0' + CAST(@RND AS VARCHAR(2)),2)
TRUNCATE TABLE #xp_results
INSERT #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME
IF EXISTS(
SELECT *
FROM msdb.dbo.sysjobs AS jobs
INNER JOIN msdb.dbo.syscategories AS categories
ON jobs.category_id = categories.category_id
INNER JOIN #xp_results AS xp_res
ON jobs.job_id = xp_res.job_id
WHERE xp_res.job_state = 1
AND jobs.name = ISNULL(@name, jobs.name)
AND categories.name = ISNULL(@category, categories.name)
AND (jobs.name <> @current_job_name OR @current_job_name is null)
)
SET @jobs_running = 1
TRUNCATE TABLE #xp_results
IF @jobs_running = 1
BEGIN
--WAITS A RANDOM NUMBER OF SECONDS BETWEEN 0 AND 60
PRINT 'WAITING ' + CAST(@RND AS VARCHAR(2)) + ' SECONDS...'
WAITFOR DELAY @DELAY_STR
END
ELSE
BEGIN
DROP TABLE #xp_results
SET NOCOUNT OFF
PRINT 'JOB COMPLETED'
RETURN
END
END -- END WHILE
DROP TABLE #xp_results
SET NOCOUNT OFF
RAISERROR('UNABLE TO WAIT FOR JOB COMPLETION, TIMEOUT EXPIRED',5,1)
END
GO
USE [TOOLS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
* Starts a SQLAgent job and waits for its completion.
* PARAMS:
* @name - Name of the job to wait for
* @timeout - Maximum number of minutes to wait for. 0 means no timeout.
*/
CREATE PROCEDURE [dba_execute_job] (@name nvarchar(255), @timeout int = 0, @step_name nvarchar(255) = null, @status int = NULL output)
AS
BEGIN
DECLARE @ret_val INT
SET NOCOUNT ON
BEGIN TRY
-- START THE JOB
if @step_name is null
EXEC @ret_val = msdb.dbo.sp_start_job @job_name = @name
else
EXEC @ret_val = msdb.dbo.sp_start_job @job_name = @name, @step_name = @step_name
IF @ret_val <> 0
RAISERROR('Unable to start the job',16,1)
-- WAIT 10 SECONDS (Should be enough in most cases)
WAITFOR DELAY '00:00:10'
EXEC dba_wait_job_completion @name = @name, @timeout = @timeout
--CHECK JOB STATUS
SELECT TOP 1 @status = run_status
FROM msdb.dbo.sysjobs jobs
INNER JOIN msdb.dbo.sysjobhistory jobhistory ON jobhistory.job_id = jobs.job_id
WHERE jobs.NAME = @name
ORDER BY run_date DESC
,run_time DESC
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
Once you have the procedure in place, you can use it like this:
EXEC [dba_execute_job] @name = 'your job name'
It will start the job and wait for completion.
-- Gianluca Sartori
February 5, 2016 at 2:39 am
Gianluca, how will using this stored procedure be more useful to the OP then just adding a jobstep at the end of each job to start the next job?
February 5, 2016 at 2:43 am
ok thanks
but when i am try to run there is an error
Could not find stored procedure 'agent.dba_wait_job_completion'.
February 5, 2016 at 2:52 am
georgheretis (2/5/2016)
ok thanksbut when i am try to run there is an error
Could not find stored procedure 'agent.dba_wait_job_completion'.
Sorry, I edited the code.
-- Gianluca Sartori
February 5, 2016 at 2:54 am
Hugo Kornelis (2/5/2016)
Gianluca, how will using this stored procedure be more useful to the OP then just adding a jobstep at the end of each job to start the next job?
It's just another way of doing things. It will let you have a single job to start the other jobs and still retain the ability to run individual jobs without executing the whole job chain.
-- Gianluca Sartori
February 5, 2016 at 3:11 am
There are a couple things to think about.
One, are these jobs dependent upon the successful completion of the previous job? If Job 1 fails, does it go on to the second job, or does it stop the entire process?
Whatever method you decide to use, you need to consider this.
Secondly, is there something that should be run, or a notification sent, in the event of a failure?
For example, Step 1 fails, send a notification and go onto Step 2. Step 2 fails, so run a completely different process, and once complete, go onto Step 3.
I guess my only other question is what do these series of jobs do?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 5, 2016 at 4:46 am
You can simply Put 10 Steps in single job. (Assign steps 1 to 10 as per sequence needed).
In case any step failes in this scenario rest steps will not execute until you choose event on failure of step either to quit job or continue after failure.
In second scenario you can create 10 Jobs. Set schedular to same time or sequentially if there is dependency.
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply