SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL agent question


SQL agent question

Author
Message
samiksc
samiksc
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 35
Comments posted to this topic are about the item SQL agent question
Kelsey Thornton
Kelsey Thornton
SSC Eights!
SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)

Group: General Forum Members
Points: 867 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
brewmanz
brewmanz
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 406
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?"
samiksc
samiksc
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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.
Kelsey Thornton
Kelsey Thornton
SSC Eights!
SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)SSC Eights! (867 reputation)

Group: General Forum Members
Points: 867 Visits: 282
sorry - I obviously missed that all-important comma in the question (and in the given "correct" answer) Wink

Kelsey Thornton
MBCS CITP
MG-148046
MG-148046
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3662 Visits: 2826
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95263 Visits: 33013
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Q -631159
Q -631159
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 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...!Smile

Q

Please take a number. Now serving emergency 1,203,894
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55601 Visits: 9730
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
mtassin
mtassin
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7106 Visits: 72521
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. Smile 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search