Determine version of SQL Server from ADO.NET

Question

I need to determine the version of SQL Server (2000, 2005 or 2008 in this particular case) that a connection string connects a C# console application (.NET 2.0). Can anyone provide any guidance on this?

Thanks, MagicAndi

Update

I would like to be able to determine the SQL Server version form the ADO.NET connection object if possible.

score 20 · Accepted Answer · edited May 23 '17 at 12:25

This code will determine the version of SQL Server database being used - 2000, 2005 or 2008:

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);

}

The code below will do the same, this time using NinthSense's answer:

try
{       
    SqlConnection sqlConnection = new SqlConnection(connectionString);
    sqlConnection.Open();

    string serverVersion = sqlConnection.ServerVersion;
    string[] serverVersionDetails = serverVersion.Split( new string[] {"."}, StringSplitOptions.None);

    int versionNumber = int.Parse(serverVersionDetails[0]);

    switch (versionNumber)
    {
        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}", versionNumber.ToString()));  
            break;  
    }
}
catch (Exception ex)
{
    MessageBox.Show(string.Format("Unable to connect to server due to exception: {1}", ex.Message),
        "Invalid Connection!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}
finally
{
    sqlConnection.Close();
}

score 15 · Answer 2 · answered Jun 04 '09 at 11:22

15

SqlConnection con = new SqlConnection("Server=localhost;Database=test;user=admin;password=123456;");
con.Open();
Text = con.ServerVersion;
con.Close();

con.ServerVersion will give you:

9.x.x for SQL Server 2005
10.x.x for SQL Server 2008

answered Jun 04 '09 at 11:22

NinethSense

8,824
2
23
23

2

The marked answer to this question is insanely complex, posted two minutes after the question was asked, and by the same author. The actual correct answer to this question is this one. Very simple one liner, as it should be. – Brain2000 Oct 19 '15 at 19:48
I recently blogged a complex, but not-sql-server-dependant solution for this - http://blog.ninethsense.com/2015/09/get-list-of-installed-sql-server.html – NinethSense Oct 21 '15 at 03:11
@NinethSense link broken – Jul 07 '20 at 19:51

score 8 · Answer 3 · answered Jun 04 '09 at 11:21

Run this script from a normal SqlCommand - it's quite extensive and useful!

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

Marc

Marc_s, Wasn't quite what I was looking for, but very useful regardless! +1 — Tangiest, Jun 04 '09 at 11:35

score 7 · Answer 4 · answered Jun 04 '09 at 10:59

7

Try

Select @@version

http://msdn.microsoft.com/en-us/library/ms177512(SQL.90).aspx

answered Jun 04 '09 at 10:59

thijs

3,445
1
27
46

thijs, I could use this, but was looking for an way of determining the version information from the ADO.NET connection. I will update the question accordingly. – Tangiest Jun 04 '09 at 11:02

score 2 · Answer 5 · answered Jun 04 '09 at 11:11

2

The Server version is also available as a (string) property on the Connection object and as a SqlVersion property on the ServerConnection.

And SQl2008 is version >= 10

answered Jun 04 '09 at 11:11

H H

263,252
30
330
514

Determine version of SQL Server from ADO.NET

5 Answers5

Linked