Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Executing SQL Server Jobs From An External Scheduler Expand / Collapse
Author
Message
Posted Tuesday, August 9, 2005 5:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 2:55 AM
Points: 142, Visits: 37
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sramakrishnan/executingsqlserverjobsfromanexternalscheduler.asp


Kindest Regards,

Sureshkumar Ramakrishnan

Post #208911
Posted Wednesday, August 24, 2005 2:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:41 PM
Points: 2,693, Visits: 1,219

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?

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #213283
Posted Wednesday, August 24, 2005 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 17, 2006 11:48 PM
Points: 1, Visits: 1

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

in MSDE SQL 2005 (aka SQL Express).

 

regards

Baudewijn Vermeire




Post #213295
Posted Wednesday, August 24, 2005 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 24, 2010 3:12 PM
Points: 2, Visits: 9
What is Control-M? I apologize, I'm still supporting SQL7 and 2000, so please be kind.

Don
Post #213404
Posted Wednesday, August 24, 2005 10:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 2:55 AM
Points: 142, Visits: 37

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

Post #213507
Posted Thursday, August 24, 2006 10:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553

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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #303883
Posted Friday, August 25, 2006 7:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:27 AM
Points: 95, Visits: 47

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

Post #304102
Posted Wednesday, May 14, 2008 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 1:46 AM
Points: 11, Visits: 76
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

Post #500402
Posted Wednesday, May 14, 2008 7:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:41 PM
Points: 2,693, Visits: 1,219
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.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #500444
Posted Friday, May 15, 2009 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 22, 2009 5:59 AM
Points: 1, Visits: 2
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,

Eb
Post #717672
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse