Microsoft KB Archive/323283

= BUG: WMI SQL Provider Does Not Work When Remotely Requesting Data from a Clustered SQL Server =

Article ID: 323283

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q323283



BUG #: 357126 (SHILOH_BUGS)



SYMPTOMS
Windows Management Instrumentation (WMI) scripts that try to access the &quot;root\MicrosoftSQLServer&quot; namespace experience problems (no results are returned) when run against a clustered SQL Server from a remote client computer (including the inactive node of the cluster).

If the same scripts are run locally on the cluster, they return results without any problems.

Running the same scripts on a non-clustered SQL Server (both locally and remotely) also return the results.

For more information, see the &quot;More Information&quot; section of this article.



WORKAROUND
The WMI Provider for SQL Server uses SQL-DMO behind the scenes to retrieve the data from SQL Server.

Instead of relying on the WMI Provider to get this data, the workarounds are to:
 * Use the SQL-DMO object model directly to retrieve the relevant data.

-or-

Make a direct connection to SQL Server (by using ODBC or OLEDB), and then run a stored procedure to retrieve the data.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The following example illustrates the problem: Filename: repro.vbs

Dim username, password, servername Dim objLocator, objServices Dim colInstances, objInstance

'******Change the values here to reflect the ClusterName, User and Pwd******* servername = &quot;VirtualServerName&quot; username = &quot;UserName&quot; password = &quot;Password&quot;

'******************************Part A: Connect to root\MicrosoftSQLServer namespace*************** wscript.echo &quot;*****Getting Values from root\MicrosoftSQLServer namespace*****&quot; Set objServices = objLocator.ConnectServer(servername, &quot;root\MicrosoftSQLServer&quot;, username, password)

objServices.Security_.ImpersonationLevel = 3

Set colInstances = objServices.InstancesOf(&quot;MSSQL_SQLServer&quot;) i = 0 For Each objInstance In colInstances i = i + 1 strServer = strServer & objInstance.Name & vbCRLF strClus = strClus & objInstance.Clustered & vbCRLF strPackage = strPackage & objInstance.Package & vbCRLF strTrueLogin = strTrueLogin & objInstance.TrueLogin & vbCRLF strTruename = strTrueName & objInstance.TrueName & vbCRLF strUserProfile = strUserProfile & objInstance.UserProfile & vbCRLF

Next wscript.echo &quot;SQLServer Name is: &quot; & strServer wscript.echo &quot;IS Clustered: &quot; & strClus wscript.echo &quot;Package: &quot; & strPackage wscript.echo &quot;TrueLogin: &quot; & strTrueLogin wscript.echo &quot;TrueName/NetworkName: &quot; & strTrueName wscript.echo &quot;UserProfile: &quot; & strUserProfile

'************************Part B: Connect to MSSQL_ConfigValue class***************** wscript.echo &quot;*****Getting Values from MSSQL_ConfigValue Class*****&quot; Set colInstances = objServices.InstancesOf(&quot;MSSQL_ConfigValue&quot;) i = 0 For Each objInstance In colInstances i = i + 1 strTemp = strTemp & objInstance.SettingID & &quot;: &quot; & objInstance.CurrentValue & vbCRLF Next

wscript.echo &quot;The number of SQL Configuration Values found is: &quot; & i & vbCRLF & vbCRLF & strTemp

Set objServices = Nothing Set objLocator = Nothing You will observer the following behavior when you run the script:
 * If you run the Repro.vbs script locally, on a clustered computer (cscript repro.vbs), both Part A and B return results.
 * If you run the Repro.vbs script locally on a non-clustered computer (cscript repro.vbs), both Part A and B return results.
 * If you run the Repro.vbs script remotely, and then connect to a non-clustered computer (cscript repro.vbs), both Part A and B return results.
 * If you run the Repro.vbs script from a client computer (cscript repro.vbs), and you then connect to a clustered server, Part A only returns the SQLServerName. Everything else is blank and Part B is totally blank.

With the preceding four (4) bullet items, it is clear that the script only fails when it is run from a client computer and it requests information from a clustered SQL Server.

Keywords: kbbug kbpending KB323283

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.