SQL Server 2008 Books Online (October 2009)
How to: Modify SQL Server Service Advanced Properties using VBScript

This section describes how to create a VBScript program that lists the version of installed instances of Microsoft SQL Server that are running on a computer.

The code example lists the instances of SQL Server running on the computer and its version.

Listing name and version of installed instances of SQL Server

  1. Open a new document in a text editor, such as Microsoft Notepad. Copy the code that follows this procedure and save the file with a .vbs extension. This example is called test.vbs.

  2. Connect to an instance of the WMI Provider for Computer Management with the VBScript GetObject function. This example connects to a remote computer named mpc, but omit the computer name to connect to the local computer: winmgmts:root\Microsoft\SqlServer\ComputerManagement. For more information about the GetObject function, see the VBScript reference.

  3. Use the InstancesOf method to enumerate a list of the services. The services can also be enumerated by using a simple WQL query and an ExecQuery method instead of the InstancesOf method.

  4. Use the ExecQuery method and a WQL query to retrieve the name and version of the installed instances of SQL Server.

  5. Save the file.

  6. Run the script by typing cscript test.vbs at the command prompt.
Example
set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")
for each prop in wmi.ExecQuery("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 AND PropertyName = 'VERSION'")
WScript.Echo prop.ServiceName & " " & prop.PropertyName & ": " & prop.PropertyStrValue
next
Tags : vxcv


Community Content

Thomas Lee
Doesn't work on VISTA machine with SQL 2008

I have a VISTA machine with SQL 2008 installed. This piece of script doesn't work at all.


[tfl - 25 08 09] Hi - and thanks for your post.You should post questions like this to the Technet Forums at http://forums.microsoft.com/technet or the MS Newsgroups at

http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quick response using the forums than through the Community Content. For specific help about:
Exchange : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.exchange%2C&
SQL Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
Windows : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows%2C&
Windows Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows.server%2C&
Virtual Server : http://groups.google.com/group/microsoft.public.virtualserver/topics?lnk
Full Public : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&


scm guy
Following my last post - it would work on SQL 2008 if ...
you add "10" to "ComputerManagement" in the first line.
Tags :

Thomas Lee
Getting SQL Server Versions using PowerShell - longer sample
<#

.SYNOPSIS
    This script displays the versions of SQL Server running on a system
.DESCRIPTION
    This script uses WMI to get the SQLServiceAdvancedProperty class from
    the ComputerManagement namespace to print out the versions. This 
    script is an adaptation of the VBS script on MSDN.
.NOTES
    File Name  : Get-SQLServerVersion.ps1
    Author     : Thomas Lee - tfl@psp.co.uk
    Requires   : PowerShell V2 CTP3
.LINK
    This script posted to:
http://www.pshscripts.blogspot.com
    MSDN Sample posted at:
http://msdn.microsoft.com/en-us/library/ms186353.aspx
.EXAMPLE


    PSH [C:\foo]: .\Get-SQLVersion.ps1'
    You are running the following versions of SQL:


    Service Name            Version
    ------------            -------
    MSSQL$MICROSOFT##SSEE   9.3.4035.00
    MSSQL$SQLEXPRESS        9.3.4035.00
#>
##
# Start of script
##



# Get the versions of SQL from WMI
$Versions = Get-WmiObject -Namespace root\Microsoft\SQLServer\computerManagement -Class SqlServiceAdvancedProperty | where {$_.SqlServiceType -eq 1 -and $_.PropertyName -eq "VERSION"} 


# Now display results
"You are running the following versions of SQL:"
"Service Name            Version"
"------------            -------"
foreach ($version in $versions) {
"{0} `t{1}" -f $version.servicename,$version.propertystrvalue



}

Thomas Lee
PowerShell Variant - a one liner

Since one-liners are all the rage, here's the one-liner version of the above:

Get-WmiObject -Namespace root\Microsoft\SQLServer\computerManagement -Class SqlServiceAdvancedProperty | where {$_.SqlServiceType
eq 1 -and $_.PropertyName -eq "VERSION"} |select Servicename,Propertystrvalue | ft -AutoSize



Here's the output:

Servicename           Propertystrvalue
-----------           ----------------
MSSQL$MICROSOFT##SSEE 9.3.4035.00
MSSQL$SQLEXPRESS      9.3.4035.00

Page view tracker