Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    63

    Talking Unanswered: Work in S.P in synchronic mode

    Hi
    I would like to know what is the way to define in specific store procedure working in synchronic Mode.


    For Example :

    CREATE PROCEDURE RS_Sample
    AS
    -- This step is execute Job
    EXEC MSDB.dbo.sp_start_job 'Refresh'

    -- This Step should be execute just when the previous was finished
    Select *
    FROm MayTable

    GO

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Job is run in SQL Server Agent account/connection. You can detect "Just finished one job named ..." by following query.

    Code:
    
    declare @JobName sysname
    set @JobName='Refresh'
    declare @Counter int
    select @Counter=count(*) 
    from msdb.dbo.sysjobs       j 
    join msdb.dbo.sysjobhistory h on j.job_id=h.job_id
    where j.name=@JobName
    EXEC MSDB.dbo.sp_start_job 'Refresh'
    while @Counter=
    (
     select count(*) 
     from msdb.dbo.sysjobs       j 
     join msdb.dbo.sysjobhistory h on j.job_id=h.job_id
     where j.name=@JobName
    ) set @Counter=@Counter
    Select * FROm MayTable
    

  3. #3
    Join Date
    Jul 2002
    Posts
    63
    Thanks on the answer.
    This cause to “wait on the While” until the job finished and it is actually solve the case,
    But do you know if there is any parameter that you can set in S.P to make it run in synchronic mode?.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    SQL Server Agent is monitoring application for MSSQLServer with distinct connection to server. This connection monitors msdb.dbo.sysdownloadlist table for changes. So parameters are passed by scaning shared table. Monitoring is probably internally supported.
    Starting normal and distributed transactions takes no effect (trying to put deadlock).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •