August 9, 2005 at 5:48 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sramakrishnan/executingsqlserverjobsfromanexternalscheduler.asp
August 24, 2005 at 2:04 am
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
August 24, 2005 at 2:58 am
The dammed thing is that the great SQL Server scheduler is not available
in MSDE SQL 2005 (aka SQL Express).
regards
Baudewijn Vermeire
August 24, 2005 at 8:01 am
What is Control-M? I apologize, I'm still supporting SQL7 and 2000, so please be kind.
Don
August 24, 2005 at 10:10 am
August 24, 2006 at 10:52 am
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.
August 25, 2006 at 7:48 am
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
May 14, 2008 at 6:41 am
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
May 14, 2008 at 7:21 am
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
May 15, 2009 at 1:44 am
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
August 20, 2010 at 10:44 am
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
September 2, 2010 at 10:10 am
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
September 2, 2010 at 10:27 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply