July 20, 2010 at 4:37 am
Hello all,
I am having a few jobs on the SQl Server that are scheduled but I sometimes (quite often actually) need to execute them on demand. Since this is happening more and more often I thought that it would be nice to allow the user to initiate the job whenever he/she feels like.
I don't want to grant access to the Management Studio though. Thus, I would like to ask for any ideas how to implement this. CMD would be an option but I also thought of a webpage. In any case, do you have any more ideas? Even a nice application could be an option.
Thanks in advance!
Theo
July 20, 2010 at 6:44 am
sorry for being brief I don't have time to work up a script but i know you can execute a job through script, however you would need to work up a stored procedure and probably give it impersonate permissions to make sure it had the rights to execute the job.
here is the msdn on sp_start_job
I would work that into your stored proc, you may want to impersonante the user you would give access so you can test whether they would have permissions to run this or if you would have to use impersonation in the stored proc
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx
Good Luck!
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 6:49 am
Hey Bradley,
Thanks for the reply! My issue is not to create a stored procedure. My issue is if anybody knows a way to allow a normal user from the finance department to start specific jobs. I don't want the users to use Management Studio. Thus, I need a third party tool or some asp/php code or any other possible solution that could do this (e.g. through a simple webpage). The jobs are related to the ERP system.
July 20, 2010 at 6:50 am
*sorry hit the tab button and enter before i finished typing:-D
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 6:53 am
Hey Theo,
I get what your saying, my suggestion would be a custom coding job. You have an app or a web page that has a button or some kind of event that can execute the job you are looking at on demand per user request (what this will do to the load of your server is a different story)
The sql mechanism to execute that job would be a stored proc that contains the t-sql code to execute that job.
impersonation would only be nessecary if the users account, or application account did not have permissions to execute the stored proc from the web page or other application that you would use to fire off the job.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 8:48 am
Any ready solutions so that I avoid coding?
I could use CMD commands for example. But are you aware of any other solutions? Even an app (not webpage) would be fine!
July 20, 2010 at 9:14 am
My appologies, I don't know of anything that would be a non-custom solution.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 9:15 am
No worries! Thanks anyway!
Anybody else having something in mind?
July 20, 2010 at 9:20 am
Do you have reporting services set up? I do a funky little thing where i create a report and just put a parameter on there thats a boolean called RunJob. If they set that to true and run the report the job runs. This is done by using an SP that called sp_start_job. That way your SP can imperonate whoever you want it too and you can use RS security to give access to the right people who can call the job.
July 20, 2010 at 9:41 am
You could look at the sqlcmd utility. The end user could call a batch file which then calls sqlcmd. This is the simplest 'interface' for a user to execute sql.
There are definite permissions issues if you want an ordinary user to start a job. You could also implement a polling architecture. A job runs every 5 minutes. This job looks to a table to see if there are any requests to run jobs. The end user wouldn't actually call the job, they would insert a request for a job to run. Your polling job would then acknowledge the request and run the job. An email could provide feedback to the user making the request.
This may be an option if your users don't need instant satisfaction. I find this polling method a little cleaner from a security point of view.
July 20, 2010 at 9:44 am
Hmmmmm I like the SSRS idea!
July 23, 2010 at 2:43 am
Ok, I created a report for this but I need to know how I can handle the sp_start_job result?
For instance when I receive the message
"Job <jobname> started successfully."
or
"Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job <jobname> (from User ***\*******) refused because the job is already running from a request by User ***\*******."
How do I pass it to the report?
Any clue?
July 23, 2010 at 3:08 am
put an output parameter varchar(200) in your sp that calls the sp_start_job procedure. Any error messages capture in a try catch loop and feed the parameter back out to your SP. then when you call the sp in the report you just need to declare a variable and then do a select after your sp call. just add that on the report
So you will have something like
Declare @MyOutput varchar(200)
exec usp_letUserRunMyJobs @JobID, @MyOutput OUTPUT
select @MyOutput
July 23, 2010 at 3:23 am
Could you please help me with the try-catch statement?
My sp looks like this:
ALTER PROCEDURE [dbo].[usp_consolidation_user_initiate]
@jobname VARCHAR(255),
@confirmation TINYINT
AS
IF @confirmation = 0
BEGIN
SELECT 'The job did not initiate because confirmation has to be set to YES.' AS result
END
ELSE IF @confirmation = 1
BEGIN
EXEC sp_start_job @jobname
END
July 23, 2010 at 4:10 am
I think try..catch statements don't work with sp_start_job.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply