April 15, 2008 at 9:49 pm
Comments posted to this topic are about the item SQL agent question
April 16, 2008 at 1:21 am
Maybe the question should have had the "on all SQL versions except EXPRESS" in there...
If you call xp_servicecontrol on EXPRESS edition and get an error, that is NOT telling you if the agent is running or not, it is simply returning an error...
Just my two penn'orth...
Kelsey Thornton
MBCS CITP
April 16, 2008 at 2:13 am
Now let's see ... I choose the first option that seems to be the only option that works for me, then I am plainly told that another option (that indeed WON'T work for me with SQLExpress) MUST be chosen, even though it's admitted that it won't work, in order to get a correct answer! Come on now; please get these question-setters showing self-consistency or setting some bounds/conditions that need to be met. Why don't I set some questions then? Mainly 'cos I'm not a DBA; I use this forum to learn stuff.
"What's 1 + 1?"
"2"
"Wrong! It's 10. I was talking binary/base 2. Didn't you know that?"
April 16, 2008 at 2:24 am
On using " xp_servicecontrol querystate, SQLSERVERAGENT "
the error shown is
"OpenService() returned error 1060, 'The specified service does not exist as an installed service.'"
Doesn't this error mean that the agent is not installed, and hence not running?
This is also a way to find out if the agent is running.
April 16, 2008 at 3:18 am
sorry - I obviously missed that all-important comma in the question (and in the given "correct" answer) 😉
Kelsey Thornton
MBCS CITP
April 16, 2008 at 6:02 am
Although most of us are guilty of using undocumented procedures, we really shouldn't promote the use of them.I spent a lot of time cleaning up a boat load of code that used undocumented stuff so we could move apps to 2005.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 16, 2008 at 6:52 am
OOPS! The "correct" answer generates an error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 7:11 am
I am not familiar with the xp_servicecontrol querystate option and cannot find any documentation on its use. In addition this option has a caveat to it. It works but not always. I believe that the answer is just to query sysprocesses for the sqlAgent and forget about undocumented procedures. Some of these undocumented procedures may have undocumented affects. I am with MG on this, I don't think we should encourage their use, but if you do want to use them, remember the explanation to this QOTD. It works except...!:)
Q
Please take a number. Now serving emergency 1,203,894
April 16, 2008 at 7:20 am
I have to say that the question and answer seem good to me. I would never have thought of even trying to do this (find out if SQL Server Agent is running with T-SQL), but I can see where it could matter for certain programs.
I had to search for data on these and find the context on them.
I think this one was good.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 8:54 am
I have to agree with GSquared. This one made me do some research to see which of these would work.
While I'm not a big fan of the undocumented things, I think the problem with them is MS should document them. 🙂 Not that they exist.
April 16, 2008 at 9:23 am
...but the command as given throws up a syntax error because the comma isn't there...
Sure, I was lazy and didn't check the syntax, and I'm not going to bitch about such meaningless things as points.
The remark about Microsoft not documenting things is a significant one - Wasn't this at the heart of some anti-competition lawsuit some time ago?
One question I do have, however. Is SQLServerAgent *ALWAYS* not installed on SQL Express? If this is not the case, does running the command on Express still give the same error if it is installed? (I hope not!)
Kelsey Thornton
MBCS CITP
April 16, 2008 at 10:16 am
The only correct answer is:
select * from sysprocesses where program_name like 'SQLAgent%'
on my dev system (MSSQL 2005 Developer) with Agent running, it returns status = "sleeping" and cmd = "AWAITING COMMAND". The query executes in 00:00.
Even if you fix the typo in other answer and run this:
xp_servicecontrol querystate, SQLSERVERAGENT
it returns Current Service State = "Running." and the query executes in 00:05.
...so the version using an undocumented sp takes much longer, and might not work in future MSSQL versions. Plus, the return value of the status is inconsistent.
April 16, 2008 at 12:02 pm
I do not. The question fails to specify the version and the answer requires use of an undocumented and potentially unsupported procedure.
-- Mark D Powell --
April 16, 2008 at 2:53 pm
xp_ServiceControl 'QueryState', 'SQLServerAgent'
works for both SQL 2000 and 2005.
Good to know this xp_.
April 16, 2008 at 3:02 pm
What happened with the primary command to get the state of the service? I mean, sc (service control) executable?
You can run it from Command Prompt as
sc query sqlserveragent
or you can run it as a query from SQL Server:
Exec xp_cmdshell 'sc query SQLSERVERAGENT'
You can also run a script or a job of the type of ActiveX script using WMI to query the state of the services and send you results by email - I use it sometimes.
I sort of would like my point back for not checking the undocumented procedure with the error in the syntax.
Regards,Yelena Varsha
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply