CODEWITHSUNDEEP
c#sqlversionlocal
elirandav
elirandav

Reputation: 2063

Get the SQL version on the machine running my app

I'm writing in c# (.Net 3.5) and I would like to get the version of the SQL installed in the local machine. It means I don't have a connection string which includes address\username\password, I just need the version of the SQL on the local machine without retrieving data from the databases etc.

I tried to use "Microsoft.SqlServer.Management.Smo.Wmi", but it seems it depends on the SQL version.

Any ideas? Thanks, KM

EDIT: Some notes,

Upvotes: 1

Views: 2543

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this in powershell:

Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement10" -Class SqlServiceAdvancedProperty -ComputerName <SERVERNAME> | Format-Table ServiceName, PropertyName, PropertyNumValue, PropertyStrValue -AutoSize

Use root\Microsoft\SqlServer\ComputerManagement10 namespace for sql server 2008 and above and root\Microsoft\SqlServer\ComputerManagement for 2005 instead.

You can achieve this result from .net using System.Management namespace.

UPDATE

You can use this to detect which kind of SQL Server WMI providers are installed.

public static IEnumerable<string> EnumCorrectWmiNameSpace()
{
    const string WMI_NAMESPACE_TO_USE = @"root\Microsoft\sqlserver";
    try
    {
        ManagementClass nsClass =
            new ManagementClass(
                new ManagementScope(WMI_NAMESPACE_TO_USE),
                new ManagementPath("__namespace"),
                null);

        return nsClass
            .GetInstances()
            .Cast<ManagementObject>()
            .Where(m => m["Name"].ToString().StartsWith("ComputerManagement"))
            .Select(ns => WMI_NAMESPACE_TO_USE + "\\" + ns["Name"].ToString());
    }
    catch (ManagementException e)
    {
        Console.WriteLine("Exception = " + e.Message);
    }

    return null;
}

Upvotes: 2

Sirwan Afifi
Sirwan Afifi

Reputation: 10824

if you want to get SQL Server Insance that installed on your machine you can use this code :

using Microsoft.SqlServer.Management.Smo.Wmi;
....

ManagedComputer mc = new ManagedComputer();

foreach (ServerInstance si in mc.ServerInstances)
{
     Console.WriteLine("The installed instance name is " + si.Name);
}

or if you want to get SQL Server Version you can use this code :

try
{
    SqlConnection sqlConnection = new SqlConnection(connectionString);
    Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection));

    switch (server.Information.Version.Major)
    {
      case 8:
        MessageBox.Show("SQL Server 2000");
        break;
      case 9:
        MessageBox.Show("SQL Server 2005");
        break;
      case 10:
        MessageBox.Show("SQL Server 2008");
                break;
      default:
        MessageBox.Show(string.Format("SQL Server {0}", server.Information.Version.Major.ToString())); 
        break;   
    }
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException)
{
    MessageBox.Show("Unable to connect to server",
        "Invalid Server", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

Upvotes: 0

Related Questions