Call SQL Server Job From CF

[NickyJay]

Hi Guys, dont know if this is the right place to ask - i assume so but appologies if not!

I have a job set up in SQL Server 2005 and need to be able to run it from my Coldfusion page. I thought i would be able to do this using a button, but im not too sure where to start.

I know there is a feature to call Stored Procedures but is there anything to call a job? I have seen the CFOBJECT tag and how it can be used to run a DTS package, but can't see anywhere if this is what i should use??

Any help/advice appreciated!!

Thanks

 

[ecobb]

Not sure about calling a Job directly. You may can do it via cfobject, but I wouldn't think so (though I've never tried).

I would suggest you ask the folks over at the SQL Server Forum (forum183) if there's a way to call a Job from a Stored Procedure. If so, just create the Procedure to call the Job, then have CF call that Procedure.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[philhege]

This T-SQL code creates and runs a self-destructing job that runs a stored procedure. You can tweak it to run existing stuff.

IF not EXISTS(select name from msdb.dbo.sysjobs where name='job_#ucase(l_user)#')
exec msdb.dbo.sp_add_job @job_name = 'myjob_#ucase(l_user)#',
@enabled = 1,
@description = 'Create Price Table for #ucase(l_user)#',
@delete_level = 1,
@notify_level_eventlog = 2,
@owner_login_name = 'loginname'
IF EXISTS(select name from msdb.dbo.sysjobs where name='job_#ucase(l_user)#')
BEGIN
exec msdb.dbo.sp_add_jobstep @job_name = 'job_#ucase(l_user)#',
@step_name = 'createwhatever',
@command = 'exec gensp_MyProcedure ''#ucase(l_user)#'', ''#dateformat(now(),"yyyy-mm-dd")#''',
@database_name = 'MyDB',
@database_user_name = 'User'

exec msdb.dbo.sp_add_jobserver @job_name = 'job_#ucase(l_user)#'

exec msdb.dbo.sp_start_job @job_name = 'job_#ucase(l_user)#'
END

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.