Executing SQL Server Jobs From An External Scheduler

Question

Executing SQL Server Jobs From An External Scheduler

Sureshkumar Ramakrishnan

Ten Centuries

Points: 1200
More actions
August 9, 2005 at 5:48 pm

#165555

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

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

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 = 0
BEGIN
  -- 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

END

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

baudewijn.vermeire SSC Rookie Points: 29 More actions · Answer 2

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

in MSDE SQL 2005 (aka SQL Express).

regards

Baudewijn Vermeire

Don Shelman Valued Member Points: 74 More actions · Answer 3

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

Don

Sureshkumar Ramakrishnan Ten Centuries Points: 1200 More actions · Answer 4

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

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]

Nali SSCommitted Points: 1525 More actions · Answer 6

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

Sameer Kapur Mr or Mrs. 500 Points: 531 More actions · Answer 7

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

philcart SSC-Forever Points: 47794 More actions · Answer 8

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

ebrahim.jacobs SSC Journeyman Points: 89 More actions · Answer 9

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

VA-322280 SSC Veteran Points: 222 More actions · Answer 10

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

Sameer Kapur Mr or Mrs. 500 Points: 531 More actions · Answer 11

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.

--Cheers

Sameer Kapur

VA-322280 SSC Veteran Points: 222 More actions · Answer 12

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