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


SQL agent question


SQL agent question

Author
Message
Kelsey Thornton
Kelsey Thornton
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 282
...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
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2114 Visits: 3057
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.
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1843 Visits: 463
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 --
Vivien Xing
Vivien Xing
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 2204
xp_ServiceControl 'QueryState', 'SQLServerAgent'

works for both SQL 2000 and 2005.

Good to know this xp_.
Yelena Varshal
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4556 Visits: 595
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 Varshal

WILLIAM MITCHELL
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2114 Visits: 3057
Two more great tips, thanks.

BTW to run the query, you need to enable 'xp_cmdshell' in Surface Area Configuration.
Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 602
On my SQL 2005 Developer installation, sysprocesses is no table, but a view (master.sys.sysprocesses), which ruled out the first answer to me (as I did only check if the table existed).

Peter Rijs
BI Consultant, The Netherlands
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 1992
I also found that you can't just query sysprocesses (unlike sysobjects), you have to specify sys.sysprocesses.

Derek
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2114 Visits: 3057
Derek,

I wonder why it doesn't work the same way for you.

I am running MSSQL 2005 SP2 Developer on Win XP Pro SP2. I open SSMS, click New Query & connect to master using Windows authentication (logged in to XP with administrator credentials) & run the query

select * from sysprocesses where program_name like 'SQLAgent%'

and it returns the one row, as previously stated. I don't need to specify the "sys." in order for it to work.

Is this another Edition or config difference?
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 1992
"select * from sysprocesses" works in 'master'. In any other database I need "select * from sys.sysprocesses". SSMS only highlights it if it's got the sys prefix.

Derek
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