Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL agent question Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2008 9:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:57 AM
Points: 12, Visits: 35
Comments posted to this topic are about the item SQL agent question
Post #485398
Posted Wednesday, April 16, 2008 1:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, Visits: 282
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
Post #485450
Posted Wednesday, April 16, 2008 2:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:05 AM
Points: 488, Visits: 400
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?"
Post #485463
Posted Wednesday, April 16, 2008 2:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:57 AM
Points: 12, Visits: 35
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.
Post #485466
Posted Wednesday, April 16, 2008 3:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, Visits: 282
sorry - I obviously missed that all-important comma in the question (and in the given "correct" answer) ;)

Kelsey Thornton
MBCS CITP
Post #485489
Posted Wednesday, April 16, 2008 6:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 1,758, Visits: 2,121
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.

Post #485580
Posted Wednesday, April 16, 2008 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 14,835, Visits: 27,311
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #485623
Posted Wednesday, April 16, 2008 7:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, Visits: 165
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

Post #485634
Posted Wednesday, April 16, 2008 7:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #485638
Posted Wednesday, April 16, 2008 8:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #485717
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse