ExecuteWQL error: An error has occured while accessing WMI: . (Microsoft.SqlServer.Dmf)

Question

Hi,

I have a problem using executeWQL for policy based management.

My statement is:

ExecuteWql('Numeric', 'root\Microsoft\SqlServer\ComputerManagement', 'SELECT PropertyStrVal FROM ServerNetworkProtocolProperty where PropertyName="TcpPort" and InstanceName="@InstanceName" and IPAddressName="IPAll"')

Error message:

An error has occured while accessing WMI: . (Microsoft.SqlServer.Dmf)

Now the problem is that the error does not provide anything useful for solving it. Does anyone has any idea what this can be?

 

Regards,

Tom

Answer 1

Hi Tom,

The problem might be the syntax error of the statement. Do you want to return the port number of the specified SQL Server instance? If that is the case, you can try to use powershell: Get-MSSQL-Port-UsingWMI.

Also, please have a look the contents at end of this article: Working with the WMI Provider for Configuration Management.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Stephanie,

Thanks a lot for looking into my question.
We are actually trying to have a centralized test-set using policy based management, without having to use any extra script-sets. For this reason, powershell is not an option.

I have done a few more checks and ruled out the possibility of a syntax error.

___To check the correct syntax, I used the WMI code generator:___
Namespace:
root\Microsoft\SQLServer\ComputerManagement

Code:

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM ServerNetworkProtocolProperty where PropertyName='TcpPort' and InstanceName='POD2008R2_TST' and IPAddressName='IPAll'",,48) 
For Each objItem in colItems 
Wscript.Echo "-----------------------------------"
Wscript.Echo "ServerNetworkProtocolProperty instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "InstanceName: " & objItem.InstanceName
Wscript.Echo "IPAddressName: " & objItem.IPAddressName
Wscript.Echo "PropertyName: " & objItem.PropertyName
Wscript.Echo "PropertyStrVal: " & objItem.PropertyStrVal
Next

Executing this code using the WMI code generator, gave a correct output.
After extracting the minimum of necessary code for ExecuteWMI, I retried the policy using the following code in the condition:

 

ExecuteWql('Numeric', 'root\Microsoft\SqlServer\ComputerManagement', 'SELECT PropertyStrVal FROM ServerNetworkProtocolProperty')

It still returned the same error message.



Now, why do I think that the syntax is correctly extracted? I used the sole example to be found of executeWql with the WMI code extractor:

Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem')

Example code used with the WMI code extractor:

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_ComputerSystem",,48) 
For Each objItem in colItems 
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "Win32_ComputerSystem instance"
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "NumberOfProcessors: " & objItem.NumberOfProcessors
Next

==> My example extracts the same string as namespace and the same part as select-statement.

Now, my guess is that the executewql from object based management does not work with all namespaces. Which I found odd, if I want to use the SQL Server namespaces. Can anyone confirm my suspicion on this or prove me wrong on this?

 

Regards,

Tom