Technical Article

VBScript to register lots of SQL Servers at once


This script will create SQL Server top level groups in EM and then register a bunch of SQL Servers in their respective groups.

The script maybe modified to as needed if the user does not wish to create groups. The script currently reads the list of servers tobe registered from a SQL Server database but could easily be changed to get that list from a text file or as a parameter.

'  Declare variables
Const ForReading = 1, ForWriting = 2
  Dim dmoApp, dmoServerGroup, dmoRegServer
  Dim fso, MyFile, strServerName

  on error resume Next

' Create a ref to the SQL Server Object
  Set dmoApp = Wscript.CreateObject("SQLDMO.Application")

' Create a ServerGroup Object
'  Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")

  Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
  dmoServerGroup.Name = "CRAWLEY"

' Add the TAMPA Server Group name
  dmoServerGroup.Name = "TAMPA"
' Register the TAMPA Servers
  Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
  dmoRegServer.UseTrustedConnection = 1
  Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
  Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt", ForReading, True)
  Do Until MyFile.AtEndOfStream
     strServerName = MyFile.Readline
     dmoRegServer.Name = strServerName
     Wscript.Echo strServerName

     WScript.Echo Err.Description

Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing