We help IT Professionals succeed at work.

How to detect if SQL Server 2005 or 2008 is installed

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
Hi,

How can I detect if SQL Server 2005 or 2008 is installed on a computer from within a Delphi application?
Preferably not by using the registry.

I found some code in C# that might do this??? (see below).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Management;
namespace ExpressDetection
{
    class Program
    {
        static void Main(string[] args)
        {
            if (!EnumerateSQLInstances())
            {
                Console.WriteLine("There are no instances of SQL Server 2005 or SQL Server 2008 installed");
            }
        }
        /// <summary>
        /// Enumerates all SQL Server instances on the machine.
        /// </summary>
        /// <returns></returns>
        public static bool EnumerateSQLInstances()
        {
            string correctNamespace = GetCorrectWmiNameSpace();
            if (string.Equals(correctNamespace, string.Empty))
            {
                return false;
            }
            string query = string.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = 'instanceID'");
            ManagementObjectSearcher getSqlEngine = new ManagementObjectSearcher(correctNamespace, query);
            if (getSqlEngine.Get().Count == 0)
            {
                return false;
            }
            Console.WriteLine("SQL Server database instances discovered :");
            string instanceName = string.Empty;
            string serviceName = string.Empty;
            string version = string.Empty;
            string edition = string.Empty;
            Console.WriteLine("Instance Name \t ServiceName \t Edition \t Version \t");
            foreach (ManagementObject sqlEngine in getSqlEngine.Get())
            {
                serviceName = sqlEngine["ServiceName"].ToString();
                instanceName = GetInstanceNameFromServiceName(serviceName);
                version = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "Version");
                edition = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "SKUNAME");
                Console.Write("{0} \t", instanceName);
                Console.Write("{0} \t", serviceName);
                Console.Write("{0} \t", edition);
                Console.WriteLine("{0} \t", version);
            }
            return true;
        }
        /// <summary>
        /// Method returns the correct SQL namespace to use to detect SQL Server instances.
        /// </summary>
        /// <returns>namespace to use to detect SQL Server instances</returns>
        public static string GetCorrectWmiNameSpace()
        {
            String wmiNamespaceToUse = "root\\Microsoft\\sqlserver";
            List<string> namespaces = new List<string>();
            try
            {
                // Enumerate all WMI instances of
                // __namespace WMI class.
                ManagementClass nsClass =
                    new ManagementClass(
                    new ManagementScope(wmiNamespaceToUse),
                    new ManagementPath("__namespace"),
                    null);
                foreach (ManagementObject ns in
                    nsClass.GetInstances())
                {
                    namespaces.Add(ns["Name"].ToString());
                }
            }
            catch (ManagementException e)
            {
                Console.WriteLine("Exception = " + e.Message);
            }
            if (namespaces.Count > 0)
            {
                if (namespaces.Contains("ComputerManagement10"))
                {
                    //use katmai+ namespace
                    wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement10";
                }
                else if (namespaces.Contains("ComputerManagement"))
                {
                    //use yukon namespace
                    wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement";
                }
                else
                {
                    wmiNamespaceToUse = string.Empty;
                }
            }
            else
            {
                wmiNamespaceToUse = string.Empty;
            }
            return wmiNamespaceToUse;
        }
        /// <summary>
        /// method extracts the instance name from the service name
        /// </summary>
        /// <param name="serviceName"></param>
        /// <returns></returns>
        public static string GetInstanceNameFromServiceName(string serviceName)
        {
            if (!string.IsNullOrEmpty(serviceName))
            {
                if (string.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase))
                {
                    return serviceName;
                }
                else
                {
                    return serviceName.Substring(serviceName.IndexOf('$') + 1, serviceName.Length - serviceName.IndexOf('$')-1);
                }
            }
            else
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// Returns the WMI property value for a given property name for a particular SQL Server service Name
        /// </summary>
        /// <param name="serviceName">The service name for the SQL Server engine serivce to query for</param>
        /// <param name="wmiNamespace">The wmi namespace to connect to </param>
        /// <param name="propertyName">The property name whose value is required</param>
        /// <returns></returns>
        public static string GetWmiPropertyValueForEngineService(string serviceName, string wmiNamespace, string propertyName)
        {
            string propertyValue = string.Empty;
            string query = String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '{0}' and ServiceName = '{1}'", propertyName, serviceName);
            ManagementObjectSearcher propertySearcher = new ManagementObjectSearcher(wmiNamespace, query);
            foreach (ManagementObject sqlEdition in propertySearcher.Get())
            {
                propertyValue = sqlEdition["PropertyStrValue"].ToString();
            }
            return propertyValue;
        }
    }
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Máté FarkasDatabase Developer and Administrator

Commented:
Or when you open a connection to the server and query this
SELECT @@Version

Open in new window

In C# at least you would use the Microsoft.SqlServer.Management.Smo.Server object to connect to each server and then examine the Properties["VersionMajor"].Value and Properties["VersionMinor"].Minor members. These would be 9 and 0 for SQL 2005, 10 and 0 for SQL 2008 and 10 and 5 for SQL 2008 R2.

Hope that helps
Stef MerlijnDeveloper

Author

Commented:
agux3e:
To which server might that be? We first have to check if there even is one (or not?)
gothamite:
I need code for Delphi 2007
Delphiwizard,
To see if there even is a SQL Server database on the server, you can attempt to connect to the database and, if there is an exception thrown, check to see the nature of the exception.  You may not be able to absolutely identify whether it exists but you can determine if you have the credentials to connect to it.
Once connected, use the SELECT @@Version; query to determine the version of the SQL Server Instance.
Stef MerlijnDeveloper

Author

Commented:
Maybe I was not clear enough, I like to get a list of available SQL server Instances (enumerate). There might be multiple.
In order to attach a database to it, I first want to be able to select the server instance the database must be attached to.

There seems to be a "Microsoft SQLDMO Type Library" for Delphi.
But in Delphi 2007 I can't find it.

Delphiwizard,
That sounds like a noticeably differnt task. ;-)  So, let me see if I can restate your question.  
You want to first determine whether there are any SQL Server instances on each available server and then, assuming that you determine that there is a SQL Server instance on a given server, you want to attach a database to that instance.  
So, you are not searching for existing databases on servers but, rather, for a server with a SQL server instance to which you can attach the database.
If that is your task, then you are going to be potentially hampered by the following:
  1. Do you have access to the servers?
  2. Assuming that you do have access to any given server, do you have sufficient access to be able to find the SQL Server instances?
  3. Assuming that you do have access and sufficient permissions to find the SQL Server Instance, do you have any access to the SQL Server instance?
  4. Assuming that you not only have access to the server and sufficient permissions on it to find the SQL Server Instance but that you also have access to the instances, do you have sufficient permissions within the instances to attach a database?
I don't quite know what your purpose is (although I am assuming the best possible reasons and that you merely want to piggy-back on existing SQL Server Instances if at all possible ;-); however, as a DBA, I would seriously doubt that I would arbitrarily give you either access to any and all of my SQL Server instances or, if I did, sufficient permisson to attach a new database to one of them.  Our network admin would probably not allow you to have sufficient permissions on any of the servers for you to access them, much less for you to be able to find any SQL Server instances.  So, I would seriously doubt that your proposed scheme would have the proverbial snowball's chance of being implemented in our world. ;-)
Top Expert 2010

Commented:
here:
http://delphi.about.com/od/sqlservermsdeaccess/l/aa090704a.htm
you can adapt it a little to suit your needs.

Stef MerlijnDeveloper

Author

Commented:
8080 diver:
Here is the thing (besides that you might watch too many paranoia movies :-):
My application would normally install an instance of SQL Server 2008 Express. But when an administrator doesn't want that, because there is already a usable running instance of SQL Server, he/she might deside to use the existing one for my application too.
If he/she desides to do the latter, then my application needs to populate a list of available servers that can be used to attach the database to.
This procedure seems perfectly oke to me in my world?
senad:
That code I currently use, but it doesn't detect an instance of SQL Server 2008.
What should be changed to solve that?

Stef MerlijnDeveloper

Author

Commented:
Hi,
I found an article that discusses the user of powershell to enumerate instances of SQL Server.
see: http://www.mssqltips.com/tip.asp?tip=2013
Maybe this can be used to solve my question. I'm just not sure how to use it in Delphi (if possible at all).

Commented:
Hi,
The Delphi 6 code below is what I use to detect an installation of SQL Server Express 2005, I'm sure it can be easily modified to suit your requirements. You'll need to import the WBEM type library... C:\WINDOWS\system32\wbem\wbemdisp.TLB

Hope it helps
unit Unit1;

interface

function IsSQLServerExpressInstalled: Boolean;

implementation

uses SysUtils, WbemScripting_TLB, ActiveX;

function IsSQLServerExpressInstalled: Boolean;
const
  SQL_EDITION = 'Express Edition';
  SQL_INSTANCE = 'MSSQL$SQLEXPRESS';
  SQL_SP_LEVEL = 1;
var
  GetSqlExpress: TSWbemLocator;
  Services: ISWbemServices;
  ObjectSet: ISWbemObjectSet;
  SObject: ISWbemObject;
  propSet: ISWbemPropertySet;
  Enum: IEnumVariant;
  tempObj: OleVariant;
  Value: Cardinal;
  //
  EditionChecked, SPLevelChecked: Boolean;
  sQry, sPropName: string;
begin
  EditionChecked := False;
  SPLevelChecked := False;
  GetSqlExpress := TSWbemLocator.Create(nil);
  try
    try
      Services := GetSqlExpress.ConnectServer('', 'root\Microsoft\SqlServer\ComputerManagement', '', '', '', '', 0, nil);
      sQry := Format('SELECT * FROM SqlServiceAdvancedProperty WHERE SQLServiceType = 1 AND ServiceName = ''%s''' +
        ' AND (PropertyName = ''SKUNAME'' OR PropertyName = ''SPLEVEL'')', [SQL_INSTANCE]);
      ObjectSet := Services.ExecQuery(sQry, 'WQL', wbemFlagReturnImmediately, nil);
      if ObjectSet.Count = 0 then
      begin
        Result := False;
        Exit;
      end;
      //
      Enum := (ObjectSet._NewEnum) as IEnumVariant;
      while (Enum.Next(1, tempObj, Value) = S_OK) do
      begin
        SObject := IUnknown(tempObj) as SWBemObject;
        propSet := SObject.Properties_;
        if propSet.Item('ServiceName', 0).Get_Value = SQL_INSTANCE then
        begin
          sPropName := propSet.Item('PropertyName', 0).Get_Value;
          if sPropName = 'SKUNAME' then
            EditionChecked := Pos(SQL_EDITION, string(propSet.Item('PropertyStrValue', 0).Get_Value)) > 0
          else if sPropName = 'SPLEVEL' then
            SPLevelChecked := (Integer(propSet.Item('PropertyNumValue', 0).Get_Value) >= SQL_SP_LEVEL);
        end;
      end;
      Result := EditionChecked and SPLevelChecked;
    except
      on E: Exception do
      begin
        //WriteLn('Error: ' + e.Message);
        Result := False;
      end;
    end;
  finally
    {ObjectSet := nil;
    SObject := nil;
    propSet := nil;
    Enum := nil;}
    GetSqlExpress.Disconnect;
    FreeAndNil(GetSqlExpress);
  end;
end;

Open in new window

DelphiWizard,
Here is the thing (besides that you might watch too many paranoia movies :-):
No, iit's not the movies but, rather, the simple fact that I deal with multiple servers with multiple database instances and mutltiple databases on a daily basis.  The reality is that your app, even in the process of being installed, may not be able to "see" various servers and/or databases instances.
As for having to populate the a list of avaialble server to which the database can be attached, if the person installing the software is an admin then shouldn't they already know what is available?  ;-)

Top Expert 2010

Commented:
remember that SQL server 2008 includes a new OLE DB provider: "SQL Server Native Client
10.0"
Stef MerlijnDeveloper

Author

Commented:
I've installed:
SQL Server 2005 Express
SQL Server 2008 Express

Senad:
With a new installation NO SQL Server instances are presented to the user.
If I manually type in the servename (Computername\ Instance) it is found however. Is there some timing problem, or restart needed?

Bongos:
Your code always return False (No Servers found). Maybe the same problem as described above.
Stef MerlijnDeveloper

Author

Commented:
Also: I found a tool called SQLPing3
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
Running this tool it shows all the servers that are installed. So how can I do this in Delphi?
Commented:
My source just looked for a specific instance of SQL Server Express, I've found a little time and modified the code to enumerate the instances based a little on the C# code you posted at the top.
function GetSQLInstances(Instances: TStrings): Boolean;
  function GetInstanceNameFromServiceName(const ServiceName: string): string;
  const
    SQLSERVER = 'MSSQLSERVER';
  begin
    if ServiceName = '' then
      Result := ''
    else
    begin
      if UpperCase(ServiceName) = SQLSERVER then
        Result := ServiceName
      else
        Result := Copy(ServiceName, Pos('$', ServiceName)+1, Length(ServiceName)-Pos('$', ServiceName));
    end;
  end;
  function GetWMIPropertyValueForEngineService(const PropertyName, ServiceName, WMINameSpace: string): string;
  var
    SWL: TSWbemLocator;
    SWServ: ISWbemServices;
    SWObjSet: ISWbemObjectSet;
    Enum: IEnumVariant;
    tempObj: OleVariant;
    Value: Cardinal;    
    SWObj: ISWbemObject;
    SWPropSet: ISWbemPropertySet;
    sQry: string;
  begin
    SWL := TSWbemLocator.Create(nil);
    try
      try
        SWServ := SWL.ConnectServer('', WMINameSpace, '', '', '', '', 0, nil);
        sQry := Format('select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = ''%s'' and ServiceName = ''%s''', [PropertyName, ServiceName]);
        SWObjSet := SWServ.ExecQuery(sQry, 'WQL', wbemFlagReturnImmediately, nil);
        if SWObjSet.Count = 0 then
        begin
          Result := '';
          Exit;
        end;
        //
        Enum := (SWObjSet._NewEnum) as IEnumVariant;
        while (Enum.Next(1, tempObj, Value) = S_OK) do
        begin
          SWObj := IUnknown(tempObj) as SWBemObject;
          SWPropSet := SWObj.Properties_;
          Result := SWPropSet.Item('PropertyStrValue', 0).Get_Value;
        end;
      except
        on E: Exception do
        begin
          //WriteLn('Error: ' + e.Message);
          Result := '';
        end;
      end;
    finally
      SWL.Disconnect;
      FreeAndNil(SWL);
    end;
  end;
const
  WMI_NAMESPACES_TO_CHECK: array[0..1] of string = ('ComputerManagement', 'ComputerManagement10');
var
  GetSqlExpress: TSWbemLocator;
  Services: ISWbemServices;
  ObjectSet: ISWbemObjectSet;
  SObject: ISWbemObject;
  propSet: ISWbemPropertySet;
  Enum: IEnumVariant;
  tempObj: OleVariant;
  Value: Cardinal;
  //
  sQry: string;
  serviceName: string;
  WMIPath: string;
  i: Integer;
begin
  Result := False;
  Instances.Clear;
  for i := 0 to 1 do
  begin
    WMIPath := Format('root\Microsoft\SqlServer\%s', [WMI_NAMESPACES_TO_CHECK[i]]);
    GetSqlExpress := TSWbemLocator.Create(nil);
    try
      try
        Services := GetSqlExpress.ConnectServer('', WMIPath, '', '', '', '', 0, nil);
        sQry := Format('SELECT * FROM SqlServiceAdvancedProperty WHERE SQLServiceType = 1 AND PropertyName = ''instanceID''', [{SQL_INSTANCE}]);
        ObjectSet := Services.ExecQuery(sQry, 'WQL', wbemFlagReturnImmediately, nil);
        if ObjectSet.Count = 0 then
        begin
          Continue;
        end;
        //
        Enum := (ObjectSet._NewEnum) as IEnumVariant;
        while (Enum.Next(1, tempObj, Value) = S_OK) do
        begin
          SObject := IUnknown(tempObj) as SWBemObject;
          propSet := SObject.Properties_;
          serviceName := propSet.Item('ServiceName', 0).Get_Value;
          // Service, Instance, Version, Edition
          Instances.Add(Format('%s, %s, %s, %s',
            [serviceName,
              GetInstanceNameFromServiceName(serviceName),
              GetWMIPropertyValueForEngineService('Version', serviceName, WMIPath),
              GetWMIPropertyValueForEngineService('SKUNAME', serviceName, WMIPath)]));
        end;
        Result := True;
      except
        on E: Exception do
        begin
          //WriteLn('Error: ' + e.Message);
          Result := False;
        end;
      end;
    finally
      GetSqlExpress.Disconnect;
      FreeAndNil(GetSqlExpress);
    end;
  end;
end;

Open in new window

Commented:
To make it run quicker, just omit the GetWMIPropertyValueForEngineService lines
Stef MerlijnDeveloper

Author

Commented:
Completely perfect. Works like a charm :-)
Thank you very much.
Is it possible grab the installation or data directory of Sql Server using this same method, similar to Version or instance name?