Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

Wait for second in Store Procedure

I have created a sp to activate the job in SQL Server. And from VB, I will call the that sp. In that sp, I need the sp to return me value of 0 or 1 to determine the process is successul or failed. But, I have 1 question. How to make the sp/vb to wait until the job activity has finished and then return the value of 0 or 1? I have tested my sp where it will not wait for the job activity to complete, and it is straight away goes to next statement in the sp.

DOes anyone can help? Please.
0
jetyun
Asked:
jetyun
  • 2
  • 2
1 Solution
 
illCommented:
i'm not sure, if i understand your question well.
in this case the sp should, according to sql books online, wait for job to finish/* haven't check it*/.

declare @tmp int
set @tmp= 1
while (@tmp>0) begin
     exec master.dbo.sp_help_job @jobname= 'readruntime', @execution_status = null
     set @tmp= @@rowcount
     waitfor delay ' 00:00:05'
end



unfortunately i can't find sp_help_job procedure in my MS SQL's 2000 (Personal Edition, Standard Edition ). Which edition do i need?  ( in which in system tables is this information stored? )
0
 
jetyunAuthor Commented:
hi ill,
 
    I m really ill now...hahaha just kidding. Nvm, let me try it and get help from Book Online.....

    Will get back to u ASAP....


Kelvsat
   
0
 
illCommented:
perhaps this way:

CREATE TABLE #res (
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, -- BOOL
request_source        INT              NOT NULL,
request_source_id     sysname          COLLATE database_default NULL,
running               INT              NOT NULL, -- BOOL
current_step          INT              NOT NULL,
current_retry_attempt INT              NOT NULL,
job_state             INT              NOT NULL
)

declare @running int
set @running= 1
while (@running>0) begin
     delete from #res
     insert INTO #res EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, dbo
     set @running= ( select running from #res where job_id= ( select job_id from msdb..sysjobs where name='myJobName' ))
     waitfor delay ' 00:00:03'
end

0
 
jetyunAuthor Commented:
cool..thanks ill. I will try and get back to u ASAP.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now