Post reply

Query to determine is the Service is running

  • June 2, 2011 at 9:08 am

    #241629

    Hello Everyone

    I am just fooling around writing a little bit is code, just bored here at work. So I am trying some new things that may come in handy in the future.

    I would like to query one server, any server on the network, just to see if it is responsive. Is there a function or a query that will tell me either way, if that SQL Instance is responsive and running, or not?

    Thanks in advance

    Andrew SQLDBA

  • June 2, 2011 at 9:39 am

    #1333192

    Hi Andrew, I believe it is a best scenario to use powershell. Take a look in get-service cmdlet or bing "service running powershell" you will find a lot of examples to how to do . 🙂

    $hell your Experience !!![/url]

  • June 2, 2011 at 9:41 am

    #1333196

    do you want a query or function that returns the status of instances from server? is that?

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • June 2, 2011 at 9:51 am

    #1333202

    Either, it does not matter.

    Andrew SQLDBA

  • June 2, 2011 at 10:05 am

    #1333210

    I agree with Laerte, PowerShell's Get-Service will give you more control and IMHO is the right tool for the job.

    http://technet.microsoft.com/en-us/library/dd347591.aspx

    However, if you must do this through T-SQL, you can use OPENDATASOURCE.

    Run this once to reconfigure your server:

    EXEC sys.sp_configure

    @configname = 'show advanced options',

    @configvalue = 1

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    EXEC sys.sp_configure

    @configname = 'Ad Hoc Distributed Queries',

    @configvalue = 1

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    Then you can do this:

    IF EXISTS ( SELECT TOP 1

    *

    FROM OPENDATASOURCE('SQLNCLI', 'Data Source=.\STD2008R2;Integrated Security=SSPI').[master].sys.objects )

    PRINT 'Server is Alive!'

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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