SQLServerCentral / Discuss Content Posted by Sureshkumar Ramakrishnan / Article Discussions / Article Discussions by Author / Executing SQL Server Jobs From An External Scheduler / Latest Posts

RE: Executing SQL Server Jobs From An External Scheduler

VA-322280 — Thu, 02 Sep 2010 10:27:56 GMT

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.VA

RE: Executing SQL Server Jobs From An External Scheduler

Sameer Kapur — Thu, 02 Sep 2010 10:10:01 GMT

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.--CheersSameer Kapur

RE: Executing SQL Server Jobs From An External Scheduler

VA-322280 — Fri, 20 Aug 2010 10:44:45 GMT

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.Thanks,VA

RE: Executing SQL Server Jobs From An External Scheduler

ebrahim.jacobs — Fri, 15 May 2009 01:44:30 GMT

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. Regards, :w00t:Eb

RE: Executing SQL Server Jobs From An External Scheduler

philcart — Wed, 14 May 2008 07:21:37 GMT

SameerAre 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.

RE: Executing SQL Server Jobs From An External Scheduler

Sameer Kapur — Wed, 14 May 2008 06:41:36 GMT

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 RegardsSameer

RE: Executing SQL Server Jobs From An External Scheduler

Nali — Fri, 25 Aug 2006 07:48:00 GMT

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).

Thanks...Nali

RE: Executing SQL Server Jobs From An External Scheduler

Robert Davis — Thu, 24 Aug 2006 10:52:00 GMT

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.

RE: Executing SQL Server Jobs From An External Scheduler

Sureshkumar Ramakrishnan — Wed, 24 Aug 2005 10:10:00 GMT

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 .

RE: Executing SQL Server Jobs From An External Scheduler

Don Shelman — Wed, 24 Aug 2005 08:01:00 GMT

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

RE: Executing SQL Server Jobs From An External Scheduler

baudewijn.vermeire — Wed, 24 Aug 2005 02:58:00 GMT

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

in MSDE SQL 2005 (aka SQL Express).

regards

Baudewijn Vermeire

RE: Executing SQL Server Jobs From An External Scheduler

philcart — Wed, 24 Aug 2005 02:04:00 GMT

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.

eg:WHILE @myFlag = 0BEGIN  -- 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 = 1END

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

Executing SQL Server Jobs From An External Scheduler

Sureshkumar Ramakrishnan — Tue, 09 Aug 2005 17:48:00 GMT

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