June 24, 2008 at 4:44 am
I have a web application through which it is possible for (privileged) users to schedule jobs. This functionality depends on SQL agent, so I need a way to see if the installation is one in which SQL agent is running in order to be able to disable or enable this part of the system. I don't want to start agent if it isn't already running. By the way, I'm also trying to write SQL that will be OK to run on SQL 2000 and SQL 2005.
Any ideas, anyone?
June 24, 2008 at 5:09 am
Well on 2005 you could check SYSPROCESSES for a process with program name 'SQLAgent - Generic Refresher'.
I'm not sure what it is on 2000.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 5:13 am
... schedule jobs ... ??
- Predefined jobs ?
- their own jobs or also jobs of other users ,
- what to the jobs do ?
* exec a sproc
* launch something from the cmdshell
....
- Is it soly your application that handles the jobs and job scheduling, or can your users also use e.g. enterprise manager or SSMS to view/manage their jobs;
I hope you take control from within your application!
Keep in mind, if realy opening this , you're opening a whole.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2008 at 5:17 am
EXEC xp_servicecontrol 'QueryState', 'SQLServerAgent'
I don't know what permissions are required to run that though. I suspect fairly high.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 5:22 am
Good idea checking SYSPROCESSES, rbarryyoung. I'll give it a try. I won't worry about SQL 2000. Although I can't have the code falling over, I'm prepared to disable the functionality if the client hasn't upgraded to SQL 2005.
I'll try EXEC xp_servicecontrol 'QueryState', 'SQLServerAgent' too, Gail. That sounds less prone to unexpected name changes!
Incidentally it will only be predefined jobs like emailing regular specific reports to people whose email addresses are already in the database.
Thanks for all your help.
Chris
June 24, 2008 at 5:25 am
Chris Hall (6/24/2008)
I'll try EXEC xp_servicecontrol 'QueryState', 'SQLServerAgent' too, Gail. That sounds less prone to unexpected name changes!
Maybe, maybe not. I think the second parameter is the service name as defined in the Services pane in windows. Not sure, as it's an undocumented proc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 5:53 am
Chris Hall (6/24/2008)
Good idea checking SYSPROCESSES, rbarryyoung. I'll give it a try. I won't worry about SQL 2000. Although I can't have the code falling over, I'm prepared to disable the functionality if the client hasn't upgraded to SQL 2005.
All you need to do is to look at sysprocesses on a SQL2000 server and see what it's program name is then, then just check for either name.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply