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 Wednesday, April 16, 2008 9:23 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
...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
Post #485750
Posted Wednesday, April 16, 2008 10:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 1,509, Visits: 2,688
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.
Post #485803
Posted Wednesday, April 16, 2008 12:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:08 AM
Points: 1,371, Visits: 385
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 --
Post #485873
Posted Wednesday, April 16, 2008 2:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:49 PM
Points: 1,279, Visits: 2,203
xp_ServiceControl 'QueryState', 'SQLServerAgent'

works for both SQL 2000 and 2005.

Good to know this xp_.
Post #485982
Posted Wednesday, April 16, 2008 3:02 PM
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: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
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

Post #485987
Posted Wednesday, April 16, 2008 3:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 1,509, Visits: 2,688
Two more great tips, thanks.

BTW to run the query, you need to enable 'xp_cmdshell' in Surface Area Configuration.
Post #485990
Posted Thursday, April 17, 2008 6:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:39 AM
Points: 898, Visits: 600
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
Post #486312
Posted Thursday, April 17, 2008 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
I also found that you can't just query sysprocesses (unlike sysobjects), you have to specify sys.sysprocesses.

Derek
Post #486535
Posted Thursday, April 17, 2008 9:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 1,509, Visits: 2,688
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?
Post #486569
Posted Friday, April 18, 2008 1:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
"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
Post #486943
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse