How to get SQL Server version only by the server name?

Question

Say, if what I have is only a server name obtained from this enumeration:

//Collect server names
List<string> arrServerNames = new List<string>();

try
{
    // Perform the enumeration
    DataTable dataTable = null;
    try
    {
        dataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();
    }
    catch
    {
        dataTable = new DataTable();
        dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
    }

    // Create the object array of server names (with instances appended)
    for (int i = 0; i < dataTable.Rows.Count; i++)
    {
        string name = dataTable.Rows[i]["ServerName"].ToString();
        string instance = dataTable.Rows[i]["InstanceName"].ToString();
        if (instance.Length == 0)
        {
            arrServerNames.Add(name);
        }
        else
        {
            arrServerNames.Add(name + "\\" + instance);
        }
    }

}
catch
{
    //Error
}

How can I know the SQL Server version installed on that server?

This links will help you http://social.msdn.microsoft.com/Forums/en-US/e14115b0-50f1-4973-a54d-d969857c0712/how-to-get-sql-server-version-informations-from-c-code?forum=adodotnetdataproviders http://stackoverflow.com/questions/11050454/how-to-check-if-sql-server-version-2008-or-higher-in-c-sharp-windows-forms — Just code, Oct 19 '13 at 07:31
possible duplicate of [How to list available instances of SQL Servers using SMO in C#?](http://stackoverflow.com/questions/1130580/how-to-list-available-instances-of-sql-servers-using-smo-in-c) — Ramesh Rajendran, Oct 19 '13 at 07:50
@c00000fd What's wrong with you ? Other people try to help you (because you are not even able to do a google research) and then you even become aggressive, because of a simple link ? Social fail. — Sliq, Oct 19 '13 at 20:43
@Panique: It seems like there's only one person who's aggressive with their comments -- you. Links posted in the first comment have nothing to do with my question. He indeed got it from Google. — c00000fd, Oct 20 '13 at 22:03

score 5 · Accepted Answer · answered Oct 19 '13 at 07:26

5

Checking the official MSDN documentation for GetDataSources() would have easily revealed that there is a Version column in the result set:

// Create the object array of server names (with instances appended)
for (int i = 0; i < dataTable.Rows.Count; i++)
{
    string name = dataTable.Rows[i]["ServerName"].ToString();
    string instance = dataTable.Rows[i]["InstanceName"].ToString();

    string version = dataTable.Rows[i]["Version"].ToString();  // this gets the version!

    ..........
}

answered Oct 19 '13 at 07:26

marc_s

732,580
175
1,330
1,459

Thanks. Just tried it but unfortunately I got `Version` as an empty string in all three entries I had in that enumeration... Just curious, can I try to connect to the SQL Server with this connection string: `("Server=" + Server + ";Integrated security=SSPI;database=master"` and then get the version from `SqlConnection`? – c00000fd Oct 19 '13 at 07:33
@c00000fd: sure, you can try - but you need to be prepared to handle the case where the connection doesn't work (if your user doesn't have "integrated security" permissions) – marc_s Oct 19 '13 at 07:34
You mean `Windows User Account` that my script runs under, right? – c00000fd Oct 19 '13 at 07:35
@c00000fd: what versions/editions are those server that all return an empty `Version` column? Just curious if that is related to e.g. the edition (e.g. Express returning empty version or something...) – marc_s Oct 19 '13 at 07:42
You know, you were right. The version property will be present only if the server has SQL Server installed and running. Otherwise it will be set to "". – c00000fd Oct 20 '13 at 22:01

How to get SQL Server version only by the server name?

1 Answers1