none
Determine version remotely

    Question

  • I hope I posted this in the correct forum....

     

    I am developing a program in .Net 2005 that gets a list of available sql servers.  Given that there are many different types of SQL servers, i am trying to figure a way to determine the specific version(s) that are on a given machine without forcing a separate login screen.  This will help to make the connection string(s) more detailed and accurate and me to make sure the correct keyword sets are available between the various platforms.  Can anybody help me with this? 

    Tuesday, December 09, 2008 3:33 PM

All replies

  •  

    Since you are using .NET 2005 you can use SMO to get the version of an instance of SQL.  The below in is visual basic, but the same functionality is available in C#. 

     

    Code Snippet

    Imports Microsoft.SqlServer.Management.Smo

    Dim local As Server

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then

    'create the Server SMO pointed to the local SQL Server

    local = New Server("(local)")

    Dim version As String

    version = local.Information.Version.ToString()

    End Sub

     

     

     

    Hope this helps
    Tuesday, December 09, 2008 4:15 PM
  • I've moved this over to SMO/DMO at David's request - it is a more appropriate forum for this question.

     

    Tuesday, December 09, 2008 4:19 PM
  • That certainly helps a bit.  I am noticing that alot of the servers do not produce version info though.  Could that be due to permissions or do older versions (like SQL 7) just not produce version info?  Also, is there any easy way/readily available way to parse down the version so that I can say what service packs, etc they are on like you can the OS on a computer?  So instead of version 9.x.xxx I can say SQL 2005 SP1 Build X??

     

    Thanks again!

     

    Koding

    Tuesday, December 09, 2008 5:01 PM
  • I believe that this is version related and attempted to get the reference, but could not locate it.

     

    As far as pre-built way to provide a user friendly version name I am not aware of any property that is provided in this method.  You could use a Case statement and compare a build list to the specific build number and provide the user friendly name.  I would Suggest using a function that would return the user friendly name based on the build number.  This sounds buredensom, but I have included hyperlinks that have the builds for 2000, 2005, and 2008 which also provide a downloadable csv file.  Hope this helps!

     

     

     

    SQL 2005 build list:

    http://www.sqlservercentral.com/articles/Administration/2960/

     

    SQL 2008 build list

    http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/

     

    SQL 2000 build list

    http://www.aspfaq.com/SQL2000Builds.asp

     

    Tuesday, December 09, 2008 5:23 PM
  • Hello Derekman,


    I will complete your code ( but as i have only Sql Server 2008 and VS 2008 SP1, you will need to adapt this code for Sql Server 2005 )


    using System;  
    using System.Collections.Generic;  
    using System.Globalization;  
    using System.IO;  
    using System.Linq;  
    using System.Text;  
    using System.Threading;  
     
    using Microsoft.SqlServer.Management.Common;  
    using Microsoft.SqlServer.Management.Sdk.Sfc;  
    using Microsoft.SqlServer.Management.Smo;  
     
    namespace VCSharp_ServerVersion  
    {  
         class Program  
         {  
              static void Main(string[] args)  
              {  
                   StreamWriter Sw = null;  
                   String AppLogName = Path.Combine(Environment.CurrentDirectory,"ServerVersion.txt");  
                   String Nl = Environment.NewLine;  
                   Server Srv = null;  
                   StringBuilder bu = null;  
                   String buf = "";  
                     
                   Thread.CurrentThread.CurrentCulture   = new CultureInfo("en-US");  
                   Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");  
                   Sw = new StreamWriter(AppLogName);  
                   Console.WriteLine("Application log name : " + AppLogName);  
                   Srv = new Server(@"LAMBIN-274404DC\SQLEXPRESS");  
                   Srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;  
                   Srv.ConnectionContext.ConnectTimeout = 75;  
                   Srv.ConnectionContext.LoginSecure = false
                   // i don't want to display my Sql Server authentification as my last Sql Server 2005 is on XP Home  
                   Srv.ConnectionContext.Login = "**";  
                   Srv.ConnectionContext.Password = "**";  
                   try  
                   {  
                        Srv.ConnectionContext.Connect();  
     
                   }  
                   catch ( ConnectionFailureException ex )  
                   {  
                        bu = new StringBuilder(Nl + "Error ConnectionFaliureException for Connect " + Nl);  
                        bu.AppendLine("Message : " + ex.Message);  
                        bu.AppendLine("Source  : " + ex.Source);  
                        bu.AppendLine("TargetSite : " + ex.TargetSite.Name);  
                        if ( ex.InnerException != null )  
                        {  
                             bu.AppendLine("InnerException : " + ex.InnerException.Message);  
                        }  
                        bu.AppendLine("StackTrace : ");  
                        bu.AppendLine(ex.StackTrace);  
                        Console.WriteLine(bu.ToString());  
                        Sw.WriteLine(bu.ToString());  
                        return;  
                   }  
                   bu = new StringBuilder("Server.Information.BuildNumber : ".PadRight(45));  
                   bu.Append(Srv.Information.BuildNumber.ToString());  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.Information.Edition : ".PadRight(45));  
                   bu.Append(Srv.Information.Edition);  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.Information.EngineEdition : ".PadRight(45));  
                   bu.Append(Enum.GetName(typeof(Edition),Srv.Information.EngineEdition));  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.Product : ".PadRight(45));  
                   bu.Append(Srv.Product);  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.ProductLevel : ".PadRight(45));  
                   bu.Append(Srv.ProductLevel);  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.Information.Version : " .PadRight(45));  
                   bu.Append(Srv.Information.Version.ToString());  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("SELECT @@VERSION : " + Nl);  
                   bu.Append(Convert.ToString(Srv.ConnectionContext.ExecuteScalar("SELECT @@VERSION")));  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Equivalent in Smo for SELECT @@VERSION : " + Nl);  
                   bu.Append(Srv.Information.Product + " ");  
                   switch ( Srv.Information.Version.Major )  
                   {  
                        case 9:  
                             bu.Append("2005");  
                             break;  
                        case 10:  
                             bu.Append("2008");  
                             break;  
                        default:  
                             bu.Append("Major " + Srv.Information.Version.Major.ToString() + " unknown");  
                             break;  
                   }  
                   bu.Append(" - " + Srv.Information.Version.ToString() + ".00  (");  
                   bu.AppendLine(Srv.Information.Platform + ")");  
                   bu.AppendLine(Srv.Information.ResourceLastUpdateDateTime.ToString("MMM dd yyyy  HH:mm:ss"));  
                   bu.AppendLine(Srv.Information.Edition + " on " + Srv.Information.OSVersion + Nl);  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.ResourceLastUpdateTime : ".PadRight(45));  
                   bu.Append(Srv.ResourceLastUpdateDateTime.ToString());  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   bu = new StringBuilder("Server.ResourceVersionString : ".PadRight(45));  
                   bu.Append(Srv.ResourceVersionString);  
                   Console.WriteLine(bu.ToString());  
                   Sw.WriteLine(bu.ToString());  
                   Sw.Flush();  
                   Sw.Close();  
              }  
         }  
    }  
     

    and the result in the .txt file ( used by the StreamWriter )


    Server.Information.BuildNumber :             3068  
    Server.Information.Edition :                 Express Edition with Advanced Services  
    Server.Information.EngineEdition :           Express  
    Server.Product :                             Microsoft SQL Server  
    Server.ProductLevel :                        SP2  
    Server.Information.Version :                 9.0.3068  
    SELECT @@VERSION :   
    Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)   
        Feb 26 2008 18:15:01   
        Copyright (c) 1988-2005 Microsoft Corporation  
        Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)  
     
    Equivalent in Smo for SELECT @@VERSION :   
    Microsoft SQL Server 2005 - 9.0.3068.00  (NT INTEL X86)  
    Feb 26 2008  20:19:50  
    Express Edition with Advanced Services on 5.1 (2600)  
     
     
    Server.ResourceLastUpdateTime :              2/26/2008 8:19:50 PM  
    Server.ResourceVersionString :               9.00.3068  
     

    To be run , this code needs some special added references but it is working versus Sql Server 2005
    Microsoft.SqlServer.Management.Sdk.dll for example

    I hope that code will help you

    Have a nice day
    Please remember to click 'Mark as Answer' on the post that helped you. Unmark if it provides no help
    Friday, December 12, 2008 9:18 AM
    Moderator