Getting a List of all available SQL Server instances

[migro]

I've embedded a little VB-script to retreive a list of all SQL Server instances on a x64 OS.

When I run the vbs-file directly all its OK. Embedded in MS it returns an "invalid parameter" error. The error occurs in this Line:
"objCtx.Add "__ProviderArchitecture", RegType". Without this Line the script returns only 32-bit Instances of the SQL Server and not the 64-bit ones. Does anybody got a hint how to prevent this error?

Here is the complete code:

Code: Select all

////////////////////////////////////////////////////////////////////////////////////////////
// Get SQL Server     Instances                                                                    //
////////////////////////////////////////////////////////////////////////////////////////////

Let>strInstNames=SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
Let>strInstanceName=Select
Let>strArchitecture=64
Gosub>GetInstance

SRT>GetInstance
Let>valuelist=
VBSTART
   Const HKEY_LOCAL_MACHINE = &H80000002
   Function GetInstanceList (RootKey, Key, RegType)
      Dim objCtx, objLocator, objReg, objServices, objInParams, objOutParams
      Dim arrInstances
      Dim lRC

      Set objCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
      objCtx.Add "__ProviderArchitecture", RegType

      Set objLocator = CreateObject("Wbemscripting.SWbemLocator")
      Set objServices = objLocator.ConnectServer("","root\default", "","",,,,objCtx)
      Set objReg = objServices.Get("StdRegProv")

      Set objInParams = objReg.Methods_("EnumKey").InParameters
      objInParams.hDefKey = RootKey
      objInParams.sSubKeyName = Key

      Set objOutParams = objReg.ExecMethod_("EnumValues", objInParams,,objCtx)

      arrInstances = objOutParams.sNames
      GetInstanceList=Join(arrInstances,vbCrLf)

   On Error Resume Next
   End Function
VBEND

VBEval>GetInstanceList(HKEY_LOCAL_MACHINE,"%strInstNames%","%strArchitecture%"),valuelist
Separate>valuelist,CRLF,vals

Let>k=0
Repeat>k
  Let>k=k+1
  Let>thisval=vals_%k%
  Let>strInstanceName=%strInstanceName%%CRLF%%thisval%
Until>k=vals_count
END>GetInstance

MessageModal>%strInstanceName%
Label>Exit

[migro]

Solution found. The corrected code looks like this now:

Code: Select all

////////////////////////////////////////////////////////////////////////////////////////////
// Get SQL Server Instances                                                               //
////////////////////////////////////////////////////////////////////////////////////////////

Let>strInstNames=SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
Let>strInstanceName=Select
Let>strArchitecture=64
Gosub>GetInstance

SRT>GetInstance
Let>valuelist=
VBSTART
   Const HKEY_LOCAL_MACHINE = &H80000002
   Function GetInstanceList (RootKey, Key, RegType)
      Dim objCtx, objLocator, objReg, objServices, objInParams, objOutParams
      Dim arrInstances
      Dim lRC

      Set objCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
      objCtx.Add "__ProviderArchitecture", RegType

      Set objLocator = CreateObject("Wbemscripting.SWbemLocator")
      Set objServices = objLocator.ConnectServer("","root\default", "","",,,,objCtx)
      Set objReg = objServices.Get("StdRegProv")

      Set objInParams = objReg.Methods_("EnumKey").InParameters
      objInParams.hDefKey = RootKey
      objInParams.sSubKeyName = Key

      Set objOutParams = objReg.ExecMethod_("EnumValues", objInParams,,objCtx)

      arrInstances = objOutParams.sNames
      On Error Resume Next
      GetInstanceList=Join(arrInstances,vbCrLf)
   End Function
VBEND

VBEval>GetInstanceList(HKEY_LOCAL_MACHINE,"%strInstNames%",%strArchitecture%),valuelist
Separate>valuelist,CRLF,vals

Let>k=0
Repeat>k
  Let>k=k+1
  Let>thisval=vals_%k%
  Let>strInstanceName=%strInstanceName%%CRLF%%thisval%
Until>k=vals_count
END>GetInstance

MessageModal>%strInstanceName%
Label>Exit

[jpuziano]

Hi migro,

I noticed when running your macro above on a machine that does not have any SQL Server instances, your Repeat/Until goes into an infinite loop.

To get around that, I added a check so that if vals_count=0 then set strInstanceName=no SQL Server Instances found

Code: Select all

////////////////////////////////////////////////////////////////////////////////////////////
// Get SQL Server Instances                                                               //
////////////////////////////////////////////////////////////////////////////////////////////

VBSTART
   Const HKEY_LOCAL_MACHINE = &H80000002
   Function GetInstanceList (RootKey, Key, RegType)
      Dim objCtx, objLocator, objReg, objServices, objInParams, objOutParams
      Dim arrInstances
      Dim lRC

      Set objCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
      objCtx.Add "__ProviderArchitecture", RegType

      Set objLocator = CreateObject("Wbemscripting.SWbemLocator")
      Set objServices = objLocator.ConnectServer("","root\default", "","",,,,objCtx)
      Set objReg = objServices.Get("StdRegProv")

      Set objInParams = objReg.Methods_("EnumKey").InParameters
      objInParams.hDefKey = RootKey
      objInParams.sSubKeyName = Key

      Set objOutParams = objReg.ExecMethod_("EnumValues", objInParams,,objCtx)

      arrInstances = objOutParams.sNames
      On Error Resume Next
      GetInstanceList=Join(arrInstances,vbCrLf)
   End Function
VBEND

SRT>GetInstance
  Let>valuelist=
  VBEval>GetInstanceList(HKEY_LOCAL_MACHINE,"%strInstNames%",%strArchitecture%),valuelist
  Separate>valuelist,CRLF,vals
  If>vals_count=0
    Let>strInstanceName=no SQL Server Instances found
  Else
    Let>k=0
    Repeat>k
      Let>k=k+1
      Let>thisval=vals_%k%
      Let>strInstanceName=%strInstanceName%%CRLF%%thisval%
    Until>k=vals_count
  EndIf
END>GetInstance

Let>strInstNames=SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
Let>strInstanceName=Select
Let>strArchitecture=64
Gosub>GetInstance
MessageModal>%strInstanceName%
Label>Exit

I also moved the VBScript block to the top as I believe that is the recommended place for VBScript... because program execution only needs to fall to those lines once... whereas if you have it in the subroutine, if the subroutine is called more than once, it will encounter the VBScript block over and over again.

I also popped the subroutine to top as well. In example macros, I have seen subroutines either:

1) all grouped at the end (most common)
2) peppered throughout a script
3) all grouped at the top

Marcus, I assume each time a subroutine is called, it starts at the top of the script and keeps reading down until it finds SRT>subroutine_name, is that correct?

If that's the case, then there is a speed advantage to always grouping subroutines at the top and further, placing the most frequently called subroutine first, 2nd most frequently called subroutine second, etc.

Normally the speed gain would be trivial, but in the case of a macro with a huge number of lines and/or a particular subroutine is called thousands or millions of times, the speed difference could really add up.

Thanks again migro for sharing this with us, much appreciated.