Is it possible to run a SQL Server 2005 job from an asp.net intranet page? I've been opening up SQL Server 2005 and manually running this and thought it would really be convenient if I could just click a button on a web page instead.
A:
You can via
msdb.sp_start_job
but it's a hassle to do it as I seem to remember that it needs to be kicked off by a connection with sysadmin priveleges (though of course if it is just a utility that you are using rather than a function on a general intranet that might not be a problem!). One way I have done similar things is to have the intranet application log a request to a table and have the job run every minute check the table and if nothing there exit or otherwise continue.
Martin Smith
2010-05-07 21:36:56
A:
I asked a similar question not too long ago and got some good results. The short answer is "Schedule an agent job when you click the button". See Remus Rusanu's answer on this question:
Run long-running sproc (that doesn’t need to return) from ASP.NET page
Joe Philllips
2010-05-07 21:51:03
Looks an interesting technique but I'm not sure what benefit it would have over and above simply scheduling the job directly by executing the relevant msdb procedure? Is there some way of getting around the security issue with this approach (e.g. with certificates)?
Martin Smith
2010-05-07 22:10:33
Well you said you want to run a "job" so I think we are all assuming this is a long-running query which you won't be able to do by clicking a button because it will timeout.
Joe Philllips
2010-05-08 00:38:43
I'm not the OP! I assumed he meant run a SQL Agent job hence the confusion, I can see it could be read two ways now though.
Martin Smith
2010-05-08 01:18:24