December 3, 2013 at 1:15 pm
Can you use a stored proc to schedule a job? What I would like to do, is that when you run the stored proc it schedules a job to run one min later, so it runs using the SQL Agent's permissions.
December 3, 2013 at 3:17 pm
It's possible to edit a job's schedule through T-SQL, certainly; I put together this kinda wonky looking bit of code. There may well be a simpler way of doing this, but here goes!
DECLARE @Time char(6)
SELECT @Time =
CASE WHEN LEN(CAST(DATEPART(HOUR,GETDATE()) AS char(2))) = 1 THEN '0'+CAST(DATEPART(HOUR,GETDATE()) AS char(2)) ELSE CAST(DATEPART(HOUR,GETDATE()) AS char(2)) END +
CASE WHEN LEN(CAST(DATEPART(MINUTE,GETDATE())+1 AS char(2))) = 1 THEN '0'+CAST(DATEPART(MINUTE,GETDATE())+1 AS char(2)) ELSE CAST(DATEPART(MINUTE,GETDATE())+1 AS char(2)) END +
CASE WHEN LEN(CAST(DATEPART(SECOND,GETDATE()) AS char(2))) = 1 THEN '0'+CAST(DATEPART(SECOND,GETDATE()) AS char(2)) ELSE CAST(DATEPART(SECOND,GETDATE()) AS char(2)) END
SELECT @Time = CAST(@Time AS Int)
EXEC msdb.dbo.sp_update_schedule
@name = (Schedule Name),
@active_start_time = @Time
Tack it onto the end of the procedure you need to run, and sub in the name of the schedule for the job you want to run. The whole mess with using DATEPART with GETDATE is because sp_update_schedule requires an INT for @active_start_time; therefore, you have to take apart GETDATE, add the minute as you requested, then concatenate the parts together, cast to INT (better to dodge the implicit conversion, I'd guess), and then run the proc to update the schedule. If you just concatenate everything together without the CASE statements, you end up dropping leading zeroes if they're present. Kinda messy.
On a side note, it would probably be more convenient if you could just use the TIME datatype with sp_update_schedule, but it won't work. Then again, there's probably a cleaner way of trimming the time down that I've blissfully overlooked.
- 😀
December 4, 2013 at 6:07 am
You're going to need some hefty permissions to use the sp_update_schedule procedure.
http://technet.microsoft.com/en-us/library/ms187354%28v=sql.100%29.aspx
December 5, 2013 at 7:45 am
Thanks, it works for me.. but even though I gave the uses execute access to the SP, it comes up with an error that the job does not exist. Is there some other securable that I need to put in the role I created for file export?
Since the SP is extracting data out of the SQL Server, it needs to run the job under the Agent permission not the user. I would perfer not to set them up with SQLAgentOperatorRole.
December 6, 2013 at 11:26 am
dwilliscp (12/5/2013)
Thanks, it works for me.. but even though I gave the uses execute access to the SP, it comes up with an error that the job does not exist. Is there some other securable that I need to put in the role I created for file export?Since the SP is extracting data out of the SQL Server, it needs to run the job under the Agent permission not the user. I would perfer not to set them up with SQLAgentOperatorRole.
If you can do the same work as the Agent job in a stored procedure, could you just create that stored proc with the WITH EXECUTE AS option so it can run with the appropriate credentials and add a call to that stored procedure to the end of the stored proc that your users execute?
Jason Wolfkill
December 9, 2013 at 7:20 am
IT has our SQL server running under a normal domian user.. thus it is not able to run properly: get the error:
Cannot execute as the user 'RADAC\willis', because it does not exist or you do not have permission.
December 9, 2013 at 11:44 am
When faced with a situation like this there are several things I'd need to know. How long does the Agent Job take to run? Are there going to be concurrent jobs running at the same time? Does it need to run "on demand" or can you "decouple" the process?
If all you need to do is trigger the process to run then you can simply devise a "enable job" process that is comprised of a Agent Job running all the time with say a 2-3 min interval watching a special table that toggles the job to run. This "decoupling" process gets you around having to assign special privileges assigned to a user to actually start the Agent Job.
I've done this when I was scrambling for a solution on a fairly large database with lots of users.
Just a though... it may or may not work in your situation... but the point here is that some solutions you need to think outside the box.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply