Stored procedure Complexity
-
Hi,
i have a stored procedure which executes a job.how can i generate an error message if the job fails in the stored procedure and get a return code for it.
CREATE
PROCEDURE [dbo].[Load_Audit_Excel]
AS
BEGIN
--interfering with SELECT statements.
SET NOCOUNT ON;
EXEC msdb.dbo.sp_start_job @job_name='Audit'
end
exitproc
:
-
I know I am getting old, but this looks like your asking the same question again. After you start the job using sp_start_job, you will need to loop inside your procedure somehow until the job finishes, and then query sysjobhistory to determine if the job suceeded of failed. Read BOL for more info on sysjobhistory and sysjobs.
-
Thanks for the reply but i am not yet cleared from it.Can you tell me on how to do it so ican get an idea for it
-
Never had a reason to do what you are trying to do. When I started a job with sp_start_job, that was all I was interested indoing as I was doing it from a trigger. I used alerts on the jobs themselves to let me know be email if they failed.
-
I'm pretty sure that somewhere in the last thread that you posted on this topic, someone posted code to perform the loop as Lynn describes.
-
I have gone back through my posts and have found four threads for this same procedure.
I really can't stress enough that you need to try to solve your problems first using
the resources available to you, including Books On Line (BOL). BOL may not answer all
your questions, but it can help you figure things out.
If you are still having problems, that is when you come asking for help. You need to
provide us with details on what you have done, instead of relying on others to do your
work for you.
What follows is psuedo code for what you need to do. I am leaving it up to YOU to figure
out the details and write your stored proc.
declare @startret int
begin
@startret = start job
if @startret = 0
begin
while run_status = 4 -- you will need to figure this out from sysjobs & sysjobhistory
waitfor delay '00:00:05' -- pause for 5 seconds
if run_status = 0
raiserror (...) -- again, read BOL
end
end
-
Hi,
i did tried the suggestion in the above post but it never went into the while loop.Any Suggestions how can i handle this .
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
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
end
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply