Executing SQL Server Jobs From An External Scheduler

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sramakrishnan/executingsqlserverjobsfromanexternalscheduler.asp

    Kindest Regards,

    Sureshkumar Ramakrishnan

  • Nice procedure. However, I'd use some sort of flagging instead of a GOTO loop. If you combine the SET assignment with a query, you can return the status with extra variables.

    WHILE @myFlag = 0
      -- check if job has completed
      SET @jobresult= ISNULL(SELECT b.last_run_outcome
        from msdb.dbo.sysjobservers b (nolock)
        where  b.job_id=@Jobid
        and convert(varchar(12),last_run_date,121)>=@lastrundate
        and last_run_time>=@lastruntime), -1) 
      IF @jobresult <> -1
        SET @myFlag = 1

    Also, under what security context does this procedure get called, and how does that affect running the jobs?


    Colt 45 - the original point and click interface

  • The dammed thing is that the great SQL Server scheduler is not available

    in MSDE SQL 2005 (aka SQL Express).



    Baudewijn Vermeire

  • What is Control-M? I apologize, I'm still supporting SQL7 and 2000, so please be kind.


  • Control-M batch scheduler is a enterprise batch management solution that is used to run and mantain external jobs .

    This is a product developed by BMC Software .

    Kindest Regards,

    Sureshkumar Ramakrishnan

  • Windows built-in Task Scheduler works great for scheduling and running DTS packages via the DTSRun utility. It even allows you to specify the Windows account to run it under. This comes in especially handy because it allows me to use external executables without installing them on the SQL Server.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Sureshkumar,

    I'm curious why you did not include SQL's native Multi-Server administration system that allows you to schedule, monitor, and report on jobs from a centralized server (MSX the Master) of other SQL servers (TSX Targets).


  • Hi Suresh,

    A nice procedure.

    Can it handle the various steps present inside the job. What if we need to pass the return status of the various steps in a job to Control M. How I can get the return value of each step for a job?

    Any ideas???

    Kind Regards


  • Sameer

    Are you running on SQL 2000 or SQL 2005?

    Thinking some sort of query on the sysjobhistory table in the msdb database would be what you're after.

    Colt 45 - the original point and click interface

  • Hi,

    I'm a Control-M administrator & know very, very little SQL. How does one use this procedure in Control-M, we currently running a batch file, i.e.:-

    "dtsrun /S omrsql064 /U xxx /P xxx /n CLASs_IMPORT Group Schemes Lead Data /e /w true"

    Will apprecaite any assistance.




  • I'm new to ControlM/EM and SQL Server Stored Procedure but have the task of creating a job where ControlM/EM will execute a SQL Server backup job (already exits in SQL Server Agent) and return a job status. I have taken your procedure and created it in my environment. When I executed the stored procedure in SQL Server 2005, I get an error message as follows: Procedure or function 'uspGetControlEMjobstatus' expect parameter '@Jobname', which was not supplied. (1 row(s) affected) (1 row(s) affected)

    Please explain.



  • The parameter in the SP expects job name to be supplied. so give the job name that you want to get executed using this procedure.


    Sameer Kapur

  • Just so that you know, I figured that part out but I was trying to figure out how ControlM talks to SQL Server. Since the writing of this, I found out that our environment does not have the ControlM for databases component turned on in ControlM and that's why I couldn't get ControlM to executed the stored procedure in SQL Server. How I resolved my problem was I got a coworker of mine to write/create a program in #C to talk to ControlM to trigger the stored procedure which executes the SQL Server backup and return a condition code as to the success or failure of the backup. Once we get the ControlM for databases component installed, then I'm going to try an set up the job to execute from ControlM directly. I hope this make sense.

    Thanks for responding.


Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply