none
ServerInstances of ManagedComputer is empty

    Question

  • I am trying to turn on TCP and Named Pipes for a given SQL Server instance.  I have used the same code on several different scenarios successfully but just encountered errors on Windows 7 64 bit.  Using SMO 2008, when I try to access the ServerInstances collection, an exception is thrown.  I have double checked and the collection is in fact empty.  The instance I am trying to access is SQLEXPRESS, although the code allows any instance to be passed.  I have double checked that the instance name being passed is correct.

    Has anyone else experienced this or does anyone know how to fix the problem?

    Code:

    Try

          Dim mc As New Wmi.ManagedComputer()
          Dim sp As Wmi.ServerProtocol
          sp = mc.ServerInstances(strInstanceName_).ServerProtocols("Tcp")

          If Not sp.IsEnabled Then blnNeedsRestart_ = True

          sp.IsEnabled = True

          sp.Alter()

          sp = mc.ServerInstances(strInstanceName_).ServerProtocols("Np")

          If Not sp.IsEnabled Then blnNeedsRestart_ = True

          sp.IsEnabled = True

          sp.Alter()

        Catch ex As Exception

          LogEvent(log_, "Failed to navigate serverinstances collection SQL Server Instance: " & strInstanceName_ & " " & ex.Source & " - " & ex.Message)
          Return False
        
        End Try

     

    • Edited by blitzlink Wednesday, June 23, 2010 1:49 PM
    Tuesday, June 22, 2010 8:56 PM

Answers

  • Solution: Explicitly set the ProviderArchitecture property to the architecture of the target SQL Server.

    If you do not explicitly set the ProviderArchitecture property, it will assume that of the running process. If the host process of your application does not match the architecture of the installed version of SQL Server on the target server, the ServerInstances collection will be empty. This is due to the separate x86 and x64 WMI providers and how SQL Server registers instances.

        Try
          Dim objManagedComputer As New ManagedComputer("target_servername")
          objManagedComputer.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit
          Dim objServerInstance As ServerInstance
          For Each objServerInstance In objManagedComputer.ServerInstances
            MsgBox(objServerInstance.Name)
          Next
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
    

     

    Friday, October 22, 2010 5:34 PM
  • I don't have a solution, but I have experienced the same problem in the following scenario

     

    1)      Installed SQLServer 2008 as a 64-bit application

    2)      Created a program to enable TCP

    3)       Built the program as a 64-bit application. The servers were reported and the TCP protocol was enabled.

    4)       Built the same program as a 32-bit application.  The servers were NOT reported.

     

    The link http://mspowershell.blogspot.com/2008/07/small-bug-in-smo.html  references the same problem.

     

    • Marked as answer by blitzlink Tuesday, July 06, 2010 1:36 PM
    Thursday, July 01, 2010 11:47 PM

All replies

  • Can you try this sample code? - This is based on SQL Powershell in SQL 2008

    http://blogs.msdn.com/b/sethus/archive/2008/10/01/enable-disable-protocols-in-sql-server-using-powershell.aspx 

     

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server

    Tuesday, June 22, 2010 11:52 PM
  • This code does seem to work, however since what I am trying to accomplish is an automated setup procedure, I would prefer to use SMO to turn on TCP and Named Pipes rather than adding the complication of having to verify if Powershell is installed and install it if it is not, and then try to automate a Powershell script.

    Can you provide an insight as to why the SMO code would cause the exception I describe?

    Wednesday, June 23, 2010 1:48 PM
  • What's the exception? Is it related to permission? I don't have sqlexpress but I tried your code on my win7 64bit and nothing is wong with ServerInstances collection.
    Hope it helps.
    Saturday, June 26, 2010 8:53 AM
  • I don't have a solution, but I have experienced the same problem in the following scenario

     

    1)      Installed SQLServer 2008 as a 64-bit application

    2)      Created a program to enable TCP

    3)       Built the program as a 64-bit application. The servers were reported and the TCP protocol was enabled.

    4)       Built the same program as a 32-bit application.  The servers were NOT reported.

     

    The link http://mspowershell.blogspot.com/2008/07/small-bug-in-smo.html  references the same problem.

     

    • Marked as answer by blitzlink Tuesday, July 06, 2010 1:36 PM
    Thursday, July 01, 2010 11:47 PM
  • Could you please report this issue at http://connect.microsoft.com ?

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server

     

    Friday, July 02, 2010 1:23 AM
  • Thanks for the info.  This is exactly the scenario that I have, however I was hoping to not have to create multiple builds of my app for x64 and x86.  My workaround was this: if I was unable to turn on tcp and named pipes due to the above scenario, I checked the TCPEnabled and NamedPipesEnabled properties of the server using SMO 2008.  This at least enabled me to inform the user if they were not on and fail with a meaningful message.
    Tuesday, July 06, 2010 1:39 PM
  • I reported the issue on July 2nd.  Title: ServerInstances of ManagedComputer is empty
    Wednesday, July 28, 2010 3:43 PM
  • Solution: Explicitly set the ProviderArchitecture property to the architecture of the target SQL Server.

    If you do not explicitly set the ProviderArchitecture property, it will assume that of the running process. If the host process of your application does not match the architecture of the installed version of SQL Server on the target server, the ServerInstances collection will be empty. This is due to the separate x86 and x64 WMI providers and how SQL Server registers instances.

        Try
          Dim objManagedComputer As New ManagedComputer("target_servername")
          objManagedComputer.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit
          Dim objServerInstance As ServerInstance
          For Each objServerInstance In objManagedComputer.ServerInstances
            MsgBox(objServerInstance.Name)
          Next
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
    

     

    Friday, October 22, 2010 5:34 PM