Jobs Running Jobs
-
Hi! I have a job I want to create to run DTSs and other jobs. Is it possible?????
Thanks,
-
Research BOL for sp_startjob
Good Hunting!AJ Ahrens
webmaster@kritter.net -
just keep in mind that sp_startjob will run asynchronusly and if you need to wait for it extra code is necessary
hth
* Noel -
Could you please give me some idea of the extra code.
In this job Step 1 will run a DTS, step 2 will run another job, step 3 will run another job, and step 4 will run a DTS. They should run sequentially.
-
At the end of this thread you can find the code to detect if a job is running or not and all you will have to code after that is someting like
exec sp_startJob ...
DECLARE
@job_name sysname,
@status tinyint
WHILE 1=1
begin
EXEC DBA..af_Job_Status 'YourJobName', @Status OUTPUT
IF @Status <> 0 --Not Done
Wait for '00:00:01' --1s
ELSE
Break
end
another option is to fire the next job at the end of the previous one and create like a job chain. It is not pretty but you don't have to code... your choice
hth
* Noel -
Here is a stored proc that i made to create a new DTS job, execute it and delete it on completion.. maybe it will give you some additional ideas to customize to your own needs.... -Mike
CREATE PROCEDURE dbo.DTSPackage_Execute
(
@UserID varchar(25) = '',
@Password varchar(25) = '',
@PackageName varchar(100)= '',
@ReturnStatus Bit OUTPUT,
@PackagePrefixIdentifier varchar(100) = 'RunDTS-')
AS
SET NOCOUNT ON
DECLARE @rs INT
DECLARE @ErrorValue INT
SELECT @ReturnStatus = 0, @ErrorValue = 0
IF LEN(@PackageName) > 0
BEGIN
DECLARE @job_id_out UNIQUEIDENTIFIER
DECLARE @ExecuteCommand varchar(1000)
DECLARE @newJobName varchar(200)
SELECT @ExecuteCommand = 'DTSRUN /S(local) /U' + @userid + ' /P' + @Password + ' /N' + @PackageName
SELECT @newJobName = @PackagePrefixIdentifier + @PackageName
-- Create a new job
EXEC @rs = msdb..sp_add_job
@job_name = @newJobName,
@enabled = 1,
@description = 'Execute a DTS Package',
@start_step_id = 1,
@notify_level_eventlog = 3,
@delete_level = 3,
@job_id = @job_id_out OUTPUT
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- Add a step to the job just created
EXEC @rs = msdb..sp_add_jobstep
@job_id = @job_id_out,
@step_id = 1,
@step_name = 'Start DTS Package',
@subsystem = 'CMDEXEC',
@command = @ExecuteCommand
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- Add job server to the job
EXEC @rs = msdb..sp_add_jobserver @job_id = @job_id_out, @server_name = '(LOCAL)'
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- run the the package now
EXEC @rs = msdb..sp_start_job @job_id = @job_id_out
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
SELECT @ReturnStatus = 1
END
ERR_HANDLER:
IF (@RS <> 0) OR (@ErrorValue <> 0)
BEGIN
SELECT @ReturnStatus = 0
EXEC @rs = msdb..sp_delete_job @job_id = @job_id_out
END
RETURN @ReturnStatus
SET NOCOUNT OFF
GO
-
Thanks! This is great ... I think we have enough to get this moving now. This forum is wonderful!
-
Depending on the object you want to execute (jobs), there are many different ways to do this.
One is the manipulate the msdb..sysjob****** tables as needed to relaunch a job. We use this to launch a few jobs when needed based on the results of the output of a stored procedure in another job.
Another concept we use for DTS packages is a DTS source data checking and execution process. We pass the name of the DTS package and its source file name to a procedure. This procedure confirms the source data exists. If it does, the data is imported. If it does not, then the DTS package is not executed. And then, if needed, we use the concept above to reschedule the import process by rescheduling this job.
-
Great code Mike. You have stop me getting less grey hairs.
Thanks
Paul
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply