HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

  • Comments 7

Have you ever wondered if there is a programmatic way to detect all the SQL server instances and services installed on a machine. Well, worry no more as the code below will do exactly that. There are 2 ways to go about this :

Method 1 – For the Programmer

The code below is written in C#.

1)       Create a new Visual C# Windows Application project.

2)       Add a RichTextBox control to your Form1.

3)       Add a Button control to your Form1 called GetmeSQL.

4)       In the Form1.cs page, add the following code.

 

//Import the Service namespace

using System.ServiceProcess;

 

5)       Right-click on the Project in “Solution Explorer” -> Add Reference. Choose System.ServiceProcess and say OK.

6)       Double-click on GetmeSQL button to take you to the code window and then copy-past the code given below.

private void GetmeSQL_Click(object sender, EventArgs e)

        {

                    string servicename = "MSSQL";

                    string servicename2 = "SQLAgent";

                   string servicename3 = "SQL Server";

                    string servicename4 = "msftesql";

 

            string serviceoutput = string.Empty;

            ServiceController[] services = ServiceController.GetServices();

            foreach (ServiceController service in services)

            {

                if (service == null)

                    continue;

                                if (service.ServiceName.Contains(servicename) || service.ServiceName.Contains(servicename2) || service.ServiceName.Contains                                  (servicename3) || service.ServiceName.Contains(servicename4))

                   {

                       serviceoutput = serviceoutput + System.Environment.NewLine + "Service Name = " + service.ServiceName + System.Environment.NewLine                                  + "Display Name = " + service.DisplayName + System.Environment.NewLine + "Status = " + service.Status +                                   System.Environment.NewLine;

                   }

            }

 

            if (serviceoutput == "")                     

                {

                        serviceoutput += "There are no SQL Server instances present on this machine!" + System.Environment.NewLine;

                }

 

            richTextBox1.Text = serviceoutput;

       }

 

7) Now build your project and bingo ! Here is how it looks :-

 

 

Method 2

Copy the code given below and save it as Filename.vbs

 strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

Set colItems = objWMIService.ExecQuery( "SELECT * FROM SqlService",,48)

For Each objItem in colItems

    Wscript.Echo "-----------------------------------"

    Wscript.Echo "SqlService instance"

    Wscript.Echo "-----------------------------------"

    Wscript.Echo "DisplayName: " & objItem.DisplayName

    Wscript.Echo "ServiceName: " & objItem.ServiceName

    Wscript.Echo "SQLServiceType: " & objItem.SQLServiceType

Next

To execute above script run it from command prompt using c:\>cscript filename.vbs or just double-click on the script.

The Service Types are documented here -> http://msdn.microsoft.com/en-us/library/ms179591.aspx

 

Method #1 will work for SQL Server 2000/2005/2008 and it can enumerate all SQL services (Database/Reporting/Analysis/Integration/FullText/Browser/Agent/VSS), whereas Method #2 works only for SQL 2005. It can be tweaked to make use of the namespace - root\Microsoft\SqlServer\ComputerManagement10 to get it to work for SQL Server 2008.

Sudarshan Narasimhan,
Technical Lead, Microsoft Sql Server

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • PingBack from http://www.clickandsolve.com/?p=19123

  • Wouldn't the first method potentially result in false positives? For example, the default service name for the MySQL server is "MySQL", which contains the string "SQL".

  • David,

    Yes you're right. I am just looking for the keyword "SQL" in the service name. You can modify the code as given below to avoid any false positivies. Now, we will still all SQL services (browser,agent,fulltext) but avoid any non-MSSQL services like MySQL etc. Thanks for bringing this to my attention.

    // Add these 3 new variables

    string servicename2 = "SQLAgent";

    string servicename3 = "SQL Server";

    string servicename4 = "msftesql";

    // Replace the IF condition given above with this

    if (service.ServiceName.Contains(servicename) || service.ServiceName.Contains(servicename2) || service.ServiceName.Contains(servicename3) || service.ServiceName.Contains(servicename4))

                       {

                           serviceoutput = serviceoutput + System.Environment.NewLine + "Service Name = " + service.ServiceName + System.Environment.NewLine + "Display Name = " + service.DisplayName + System.Environment.NewLine + "Status = " + service.Status + System.Environment.NewLine;

                       }

  • string servicename3 = "SQL Server"; does not work for SQL 2005 Express

    you may use string servicename3 = "MSSQL$" instead to find out the instance of the installed server, but it wont work for 2000.

  • Dear Sir i had tired the Method 2 , BUt it giving me the  Invalid syntax Error can you pls tell me what should i do . I am using SQL server 2008 R2.

    And i want to know how many features are installed in my SQL server 2008 R2???

  • Hi Rahul,

    Thanks for bringing this up. There seems to be a typo in Line #3 of the script. Please remove the _(underscore) character that comes after Execquery and retry the script. Also as mentioned in the post, if you are using Sql 2008R2 then you need to change the namespace to computermanagement10 to get it to detect SQL 2008+ instances.

    [Change Line #3 as shown below]

    Set colItems = objWMIService.ExecQuery("SELECT * FROM SqlService",,48)

    [Change namespace as shown below]

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")

    -Sudarshan

  • Thanks Very Much

Page 1 of 1 (7 items)