Does anyone have a WQL Query for finding the Edition of a SQL Server Instance?

Question

Does anyone know how to use WMI to get the Edition of an SQL Server installation (do not care about the version) in VBScript?

I have tried using the registry (but that requires you to know the instance name and the version of the SQL Server.

I have tried using an SQL Query (but that requires you to have permissions upon the database and my process is using the LOCAL user, with no permissions on the SQL Server DBMS).

So, I am left with using WQL to query WMI.

I guess that I need to:

1) query a WMI object (which one) to get the instance names. 2) Then, for each instance, query another object and get the Edition out of it.

I have looked through the Microsoft documentation and I cannot find the objects to use.

Can anyone help?

QuietLeni

score 2 · Answer 1 · answered Dec 03 '12 at 20:59

Start here: How to: Modify SQL Server Service Advanced Properties using VBScript

The first sample:

set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement10")
for each prop in wmi.ExecQuery("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 AND PropertyName = 'VERSION'")
    WScript.Echo prop.ServiceName & " " & prop.PropertyName & ": " & prop.PropertyStrValue
next

seems to do what you asked for. Check "Other Versions" if you are not using version 2008.

score 2 · Answer 2 · answered Dec 04 '12 at 10:11

Thanks for that. Here is the script that I created. I hope that it helps someone else in my position:

Dim strValueName, strSKUName, strEdition, strVersion, strArchitecture 
Dim objWMI, objProp

On Error Resume Next
' First try SQL Server 2008/2008 R2:
Set objWMI = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement10")
If Err.Number <> 0 Then
    ' Next, try SQL Server 2005:
    Set objWMI = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")
    If Err.Number <> 0 Then
        ' Next, try SQL Server 2012:
        Set objWMI = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement11")
    End If
End If


If Err.Number = 0 Then

    On Error Goto 0
    ' Go through the properties (which is just one) and find the name of the SKU.
    For Each objProp In objWMI.ExecQuery("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 AND (PropertyName = 'SKUNAME' OR PropertyName = 'VERSION')")
        If objProp.PropertyName = "SKUNAME" THEN
            strSKUName = objProp.PropertyStrValue
        Else
            strVersion = objProp.PropertyStrValue
        End If
    Next

    ' We do not want the number of bits, so chop it off!
    If Instr(strSKUName, " (") <> 0 Then
        strEdition = Left(strSKUName, Instr(strSKUName, " ("))
        strArchitecture = "64-bit"
    Else
        strEdition = strSKUName
        strArchitecture = "32-bit"
    End If


    WScript.Echo strEdition & " / " & strSKUName & " / " & strArchitecture

End If

Does anyone have a WQL Query for finding the Edition of a SQL Server Instance?

2 Answers2