JJ B (6/5/2009)
Example of where I could probably use this (or a similar) technique: We use SQL Server 2005. I have several SSIS packages that fail at random intervals and at random steps/tasks. The failure notice is that the poor thing just failed to acquire a connection to our own database right there in the same server as SSIS. The scheduled job that fails one night is likely to run fine the next next night with no changes. Once, however, I got the same error an entire week before the job started working again. (We re-boot our server every week.) Sometimes I get the same error when I manually run the job, and and then a minute later it works.
Fine. We can usually live with this buggy behavior. However, this problem unfortunately happened once when I was on vacation. It would have been good if I had had a solution such as the one shown in this article so that my users could have manually run the job themselves when they needed it.
I understand that security issues have to be worked out, but giving the users the ability to run a job on demand in a production environment could have some very practical purposes in my agency.
Why does anyone live with buggy behavior. Solve the Issue. Get Profiler running and find the Table lock or constraint that is throwing the error. It's not Buggy Behavior it's bad development and lack luster administration.
sp_start_job requires permission to execute higher level processes.
The best way to get a simple user to be able to fire a Job on Demand is:
1) Build a table in a public database [Jobs] (Job_name,Server,Database,RequestUser,Requestdate,StartDate,CompleteDate,JobStatus)
Then a user can insert a record when they want the job to execute. via any Client(WEB,Winform,access,SSMS,etc.....)
Create a Second Job call USer Jobs running under sql agent as a domain sysadmin
with a schedule to run every 5 minutes (or whatever meets your needs)
Cursor Select Job_name from Jobs where StartDate is null
exec msdb..sp_start_job @Job_name=@Job_name
The table can be as elaborate as you need
The user jobs Job can update the status and manage Jobs with any business rules.
I am sorry if I have offended, this is my second post here.
I won't be back