Get SQL Server version of each instance installed

Discussion in 'Scripting' started by Carlos Felipe França da Fonseca, Sep 18, 2008.

  1. I'm looking for a way to get the version of each SQL Server instances
    installed locally on a server.
    Does anybody know the best way of doing that?

    Thanks,

    Felipe
     
    Carlos Felipe França da Fonseca, Sep 18, 2008
    #1
    1. Advertisements

  2. I use a VBScript program. You would need a connection string for each
    instance.
    ===========
    ' Retrieve SQL Server version.

    Option Explicit

    Dim strConnect, adoConnection, adoRecordset, objShell
    Dim strVersion, strLevel, strEdition

    ' Construct connection string for PocketLunch database.
    strConnect = "DRIVER=SQL Server;" _
    & "Trusted_Connection=Yes;" _
    & "DATABASE=Master;" _
    & "SERVER=MyServer\MyInstance"

    ' Create ADO objects and connect to PocketLunch database.
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.ConnectionString = strConnect
    adoConnection.Open

    Set adoRecordset = CreateObject("ADODB.Recordset")
    Set adoRecordset.ActiveConnection = adoConnection

    ' Retrieve balances for each account as of specified date.
    adoRecordset.Source = "SELECT SERVERPROPERTY('productversion') AS Version, "
    _
    & "SERVERPROPERTY('productlevel') AS Level, " _
    & "SERVERPROPERTY('edition') AS Edition"
    adoRecordset.Open

    ' Enumerate account balances.
    Do Until adoRecordset.EOF
    strVersion = adoRecordset.Fields("Version").Value
    strLevel = adoRecordset.Fields("Level").Value
    strEdition = adoRecordset.Fields("Edition").Value
    Wscript.Echo "Version: " & strVersion
    Wscript.Echo "Level: " & strLevel
    Wscript.Echo "Edition: " & strEdition
    adoRecordset.MoveNext
    Loop

    ' Clean up.
    adoRecordset.Close
    adoConnection.Close
    ==========
    Otherwise, you can run the query (the Source property of the ADO Recordset
    object above) with a command line tool or GUI.
     
    Richard Mueller [MVP], Sep 18, 2008
    #2
    1. Advertisements

  3. Thanks for your reply, Richard.

    I'm going to run this solution on many servers, locally and monthly using
    Tivoli monitoring. The reason of running that is for auditing purposes.
    The problem is that I don't know how many instances each server is running.
    So, we'll need to discover how many instances the server is running and also
    their respective versions.
    We'll confirm if all licences for all instances are available and if each
    server really has the number of instances mentioned in the support contract.

    Thanks again,

    Felipe
     
    Carlos Felipe França da Fonseca, Sep 18, 2008
    #3
  4. I looked for a way to enumerate all instances, but the only solution I found
    used .NET. There must be an SQL query (when you connect to the default
    instance) to do this, but I could not find it. One way would be to run "net
    start" and parse everything that starts with MSSQL$ for instance names,
    except under newer versions of SQL the service seems to be named:

    SQL Server (<instance name>)

    --
    Richard Mueller
    MVP Directory Services
    Hilltop Lab - http://www.rlmueller.net
    --

     
    Richard Mueller [MVP], Sep 18, 2008
    #4
  5. I got it using registry keys:

    strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"

    Set
    objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    objReg.EnumValues &H80000002, strKeyPath, arrEntryNames, arrValueTypes

    For count=0 To UBound(arrEntryNames)
    objReg.GetStringValue &H80000002, strKeyPath, arrEntryNames(count),
    strInstance
    objReg.GetStringValue &H80000002, "SOFTWARE\Microsoft\Microsoft SQL
    Server\" & strInstance & "\Setup", "Version", strVersion
    objReg.GetDWORDValue &H80000002, "SOFTWARE\Microsoft\Microsoft SQL
    Server\" & strInstance & "\Setup", "SQLCluster", strIsClustered
    If strIsClustered = 0 Then strIsClustered = "Stand-alone" Else
    strIsClustered = "Clustered"
    Wscript.Echo count+1 & ". " & arrEntryNames(count) & " - " & strVersion
    & " - " & strIsClustered
    Next

    Thank you again for your efforts, Richard! ;)

    Regards,

    Felipe

     
    Carlos Felipe França da Fonseca, Sep 19, 2008
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.