I know this is a while ago but just in case anyone is looking......

A technique I use is to look in the job history. The history record is only written when the job completes. Try this:

CREATE PROC

RunMyJob

AS

BEGIN

SET NOCOUNT ON

DECLARE @LastID BIGINT

SELECT

@LastID = MAX(S.instance_id)

FROM

msdb.dbo.sysjobhistory S

join

msdb.dbo.sysjobs SJ

ON

S.job_id = SJ.job_id

WHERE

SJ.name = 'MyJob'

EXEC msdb.dbo.sp_start_job 'MyJob'

WHILE NOT EXISTS

(

SELECT

*

FROM

msdb.dbo.sysjobhistory S

join

msdb.dbo.sysjobs SJ

ON

S.job_id = SJ.job_id

WHERE

SJ.name = 'MyJob'

AND

S.instance_id > @LastID

)

BEGIN

WAITFOR DELAY '00:00:01'

END

……more code here

END

so, try every one second to see if the history record is there (you need to make sure there is at least one history record before the sproc runs). Once the history record is there, you code can continue confident that the job has completed (but not necessarily successfully).

Have fun

TheSpyder