Get SQL Server version of each instance installed

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

Felipe wrote:

> 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?
>

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 Directory Services
Hilltop Lab - http://www.rlmueller.net
--

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

"Richard Mueller [MVP]" <rlmueller-> wrote in
message news:...
> Felipe wrote:
>
>> 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?
>>
>
> 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 Directory Services
> Hilltop Lab - http://www.rlmueller.net
> --
>
>

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

"Carlos Felipe França da Fonseca" <> wrote in
message news:...
> 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
>
> "Richard Mueller [MVP]" <rlmueller-> wrote in
> message news:...
>> Felipe wrote:
>>
>>> 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?
>>>
>>
>> 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 Directory Services
>> Hilltop Lab - http://www.rlmueller.net
>> --
>>
>>
>
>

I got it using registry keys:

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

Set
objReg=GetObject("winmgmts:{impersonationLevel=imp ersonate}!\\.\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

"Richard Mueller [MVP]" <rlmueller-> wrote in
message news:...
>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
> --
>
> "Carlos Felipe França da Fonseca" <> wrote in
> message news:...
>> 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
>>
>> "Richard Mueller [MVP]" <rlmueller-> wrote in
>> message news:...
>>> Felipe wrote:
>>>
>>>> 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?
>>>>
>>>
>>> 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 Directory Services
>>> Hilltop Lab - http://www.rlmueller.net
>>> --
>>>
>>>
>>
>>
>
>