Post reply

sp_services

  • November 17, 2009 at 9:16 pm

    #140039

    Hi Guys,

    I came across a question yesterday in SSC as below:

    SQL agent question:

    Using Transact-SQL, how can you find out whether SQLAgent is running? (Select all that apply)

    Answer Options were:

    sysprocesses

    xp_servicecontrol

    sp_services

    I checked all as the answer because I thought all these sps and table give the relevant info about the SQL AGENT service.

    But SSC gave wrong for sp_services.

    Though it was in SQL2K, but still it gives the state of SQL Agent right?

    PLZ Correct me if I'm wrong.

    Thanks in advance

    John

  • November 18, 2009 at 5:45 am

    #1080820

    This query will give you the desired output

    xp_servicecontrol 'querystate', 'SQLServerAgent'

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 18, 2009 at 2:51 pm

    #1081237

    I want to know about sp_services.

    Because SQL server central gave wrong for sp_services in its question of the day contest. But I still believe that this SP can be used to find the 'SQL AGENT service'

    sp_services contains the following code which fetches the state of the services in a machine:

    xp_cmdshell 'sc query state= all'

    John

  • November 18, 2009 at 11:28 pm

    #1081369

    --sp_services contains the following code which fetches the state of the services in a machine:

    --xp_cmdshell 'sc query state= all'

    Their is no such sytem Stored Procedure "sp_services" in SQL2000/2005.

    From where you got that sp_services contains xp_cmdshell 'sc query state= all' ....

    Rd,

    Deepali

  • November 22, 2009 at 7:24 am

    #1082710

    I got it from here:

    http://www.hotcoding.com/dbs/sql/34936.html

    Correct me if I'm wrong

    Thanks in advance

    John

  • November 22, 2009 at 9:47 am

    #1082722

    thats a user SP not a system one. it just so happens to have the same name as one of the answers in the SSC question. probably one good reason among others to not name user SP's SP_xxxx, name them USP_xxxx or something else so you dont get confused

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply