?
Solved

Wait for second in Store Procedure

Posted on 2003-03-25
4
Medium Priority
?
447 Views
Last Modified: 2008-02-01
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
Comment
Question by:jetyun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:ill
ID: 8208741
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
 

Author Comment

by:jetyun
ID: 8208940
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
 
LVL 12

Accepted Solution

by:
ill earned 400 total points
ID: 8209295
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
 

Author Comment

by:jetyun
ID: 8214821
cool..thanks ill. I will try and get back to u ASAP.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question