2

I have few SQL Server Agent Jobs running in my project. The jobs run perfectly as scheduled, no issues.

But now I need to be able to start these jobs from the front end (Like on a click of button or so).

How can I do it ?

Do these jobs behave just like a functions ?

oceandrive
  • 41
  • 1
  • 2

2 Answers2

3

You can do this with any db connector I've tried--here are a couple examples...

Using CallableStatement: 

Connection rConn = //however you get your connection...
CallableStatement cs = rConn.prepareCall("EXEC dbo.sp_start_job N'your job name'");
boolean checkvar = cs.execute();

Alternatively, if you use a jdbc template:

jdbcTemp = //however you get your template...
jdbcTemp.update("EXEC msdb.dbo.sp_start_job N'" + procName + "'");

Also, you will likely need to adjust the permissions of the msdb in order for this to work. Your account needs to either be a sysadmin or have the SQLAgentOperatorRole role. To set this in SQL Server Management, go to Security under your db engine, expand logins, right click on the account you will use and select properties. Under Server Roles you can grant sysadmin, or under User Mapping check msdb, then select TargetServersRole and SQLAgentOperatorRole from the list below.

hth

plattitude
  • 187
  • 1
  • 1
  • 11
  • 1
    Funny story--last night our DBAs hosed the database I had set the permissions on as I indicated above, causing our application to start failing. I couldn't remember the permissions that were needed, but I remembered posting about it to StackOverflow. Posting here saved me hours of re-looking up the correct permissions! – plattitude Mar 05 '13 at 14:51
1

you can call it by using the sp_startjob proc

example

EXEC msdb.dbo.sp_start_job N'MyJobName';
SQLMenace
  • 132,095
  • 25
  • 206
  • 225