Sp_start_job in stored procedure(SSIS)
-
Hi ,
i need help on the following issue
I have a stored procedure "Load_Audit_Excel " which executes a job and does the select from the Job_Log Table.
The issue is even before the job execution is done the select statement is invoked.i want the select statement to wait until the job executes.
The reason i want to this is because iam updating the Job_Log Table in the SSIS package which is run through the job which i execute in the first step
Thanks
CREATE
PROCEDURE [dbo].[Load_Audit_Excel]
(
@Return_Code int output
)
AS
begin
Truncate
Table Job_Log
Insert
into
Job_Log
(
JobStartTime
, ErrorCode)
values
(
Getdate(), 1)
exec
@Return_Code=msdb.dbo.sp_start_job @job_name='Audit'
Select
ErrorCode from Job_Log
Select
@Return_Code
end
exitproc
:
----------------------------------------------------------------------------------------------
CREATE
PROCEDURE [dbo].[Jobs_Log]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Update Job_Log
SET ErrorCode = 0
select
ErrorCode from Job_Log
END
-
As has been explained in the several other threads you have posted, all that msdb.dbo.sp_start_job does is start a job. It returns immediately after the job been started. It does not wait for the job to finish before returning (ie, it is asyncronous).
I provided you with a short (and rough) psuedo for what you need to do if you want to have the stored procedure wait until the job has completed to continue processing. What have you done in regards to working with that psuedo code? What problems have you encountered in trying to write this procedure yourself.
There are many of us out here who are ready and willing to help, but you have to so that you are also willing to help yourself and try to come up with more than you have so far.
-
i did tried the suggestion stated in the above post but it never worked as it does not get into the while loop.
Any Suggestions
Thanks
----------------------------
declare
@startret int
declare
@run_status int
begin
exec @startret = msdb.dbo.sp_start_job @job_name='Audit'
if @startret = 0
begin
select
@startret
while (select top(1) run_status from msdb.dbo.sysjobhistory)= 4 -- you will need to figure this out from sysjobs & sysjobhistory
begin
waitfor delay '00:00:05' -- pause for 5 seconds
select
@startret
end
if (select top(1) run_status from msdb.dbo.sysjobhistory)= 0
select @startret -- again, read BOL
end
end
-
Here is a hint: You will need to join sysjobs and sysjobhistory to identify the job you you are looking for by name (Audit). You want to loop as long as the job is running.
-
Any luck yet with this procedure? What problems are you encountering?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply