SQL agent question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sameeksha » SQL agent question

27 posts, Page 2 of 3

««1 2 3 »»

SQL agent question

Rate Topic

Display Mode

Topic Options

Author

Message

Kelsey Thornton

Posted Wednesday, April 16, 2008 9:23 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281

...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

WILLIAM MITCHELL

Posted Wednesday, April 16, 2008 10:16 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441, Visits: 2,485

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

Mark D Powell

Posted Wednesday, April 16, 2008 12:02 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:26 PM
Points: 1,258, Visits: 341

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

Vivien Xing

Posted Wednesday, April 16, 2008 2:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:19 AM
Points: 1,279, Visits: 2,191

xp_ServiceControl 'QueryState', 'SQLServerAgent'

works for both SQL 2000 and 2005.

Good to know this xp_.

Post #485982

Yelena Varshal

Posted Wednesday, April 16, 2008 3:02 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

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

WILLIAM MITCHELL

Posted Wednesday, April 16, 2008 3:09 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441, Visits: 2,485

Two more great tips, thanks.

BTW to run the query, you need to enable 'xp_cmdshell' in Surface Area Configuration.

Post #485990

Peter Rijs

Posted Thursday, April 17, 2008 6:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:03 AM
Points: 856, Visits: 573

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
MCITP BI Dev & DB Dev (SQL 2008 & 2005)

Post #486312

Derek Dongray

Posted Thursday, April 17, 2008 9:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

I also found that you can't just query sysprocesses (unlike sysobjects), you have to specify sys.sysprocesses.

Derek

Post #486535

WILLIAM MITCHELL

Posted Thursday, April 17, 2008 9:47 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441, Visits: 2,485

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

Derek Dongray

Posted Friday, April 18, 2008 1:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

"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 »

27 posts, Page 2 of 3

««1 2 3 »»

Permissions