Post reply

command to get all sqlserver instances running on Server?

  • November 15, 2006 at 11:32 am

    #61980

    Does anyone know of a command that can be ran on a Windows server that will show the name of all of the SQL Server instances? Looking for something similar to

    ‘ps –ef|grep pmon’ on Oracle.

    We are needing this so that the Windows team can put together a script to run on all servers and pull back instance (not database) names for entry.

  • November 15, 2006 at 12:45 pm

    #672106

    start->run cmd-> ISQL -L

     

    Kindest Regards,

    Vasc

  • November 15, 2006 at 12:50 pm

    #672109

    are you sure this is the correct command? This gave me a long listing of many systems.

  • November 15, 2006 at 1:17 pm

    #672117

    SQLCMD -L

    Kindest Regards,

    Vasc

  • November 15, 2006 at 1:35 pm

    #672121

    They can interrogate the services themselves. The display names:

    SQL Server 2000 default instances run under:

 MSSQLSERVER

SQL Server 2005 default instances run under:

 SQL Server (MSSQLServer) 

SQL Server 2000 Named Instances:

 MSSQL$<Instance Name>

SQL Server 2005 named instances:

 SQL Server ( <Instance Name> )

    As far as the service names themselves, they'd be MSSQLSERVER (default instance) and MSSQL$<Instance Name> for both 2000 and 2005.

    And the -L doesn't work if:

    1) The server is hidden (meaning it's listening on tcp/2433

    2) The SQL Server listener service is blocked by an IPSEC policy on the server or by a firewall.

    3) udp/1434 is blocked on the network

    4) All instances of SQL Server are turned off.

    K. Brian Kelley
    @kbriankelley

  • November 16, 2006 at 2:28 pm

    #672461

    Hi...

    I have used this command to get all the instances from SQL Server for a software licensing app.  It's run from within a vb6 app and I just shell out of the app and run it.  Once vb6 shells out, it is run in conjunction with psexec by remotely connecting to suspected SQL Servers/PCs and running this command.

    All you need to do is change the host name.

    sc  \\< Host Name > query state= all | find "SERVICE_NAME" | find "MSSQL"

    Output looks something like:

    SERVICE_NAME: MSSQL$SQLEXPRESS

    SERVICE_NAME: MSSQLServerADHelper

    The only issue that this has is it picks up the MSSQLServerADHelper service and also the MSSQLServerOLAPService service if installed, however it is easy to exclude these two via your chosen method.  Mine method is vb6.

    Hope this helps

    Brett

    Thanks

  • November 16, 2006 at 4:45 pm

    #672498

    This works well, too. If, for some reason, you're on Windows 2000 still (not 2003 or XP), I believe sc is available for download from Microsoft as part of the 2000 Resource Kit.

    K. Brian Kelley
    @kbriankelley

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

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