Tuesday, February 19, 2008

Loading all SQL Servers into SSMS From Network (VBScript)

Previously I posted how to enumerate your SQL Servers into the XML regsrvr file that can be imported into SSMS to automatically register all SQL Servers on your domain in SSMS. Knowing that not everyone would want to install Visual C# or Visual Studio to build the console app, I also worked on the following VBScript which does the same exact job, using the same tools.

Script:

Set fso = CreateObject("Scripting.FilesystemObject")
Set objSQLDMOApp = CreateObject("SQLDMO.Application")

Set objSQLList = objSQLDMOApp.ListAvailableSQLServers()

set a = fso.createtextFile("c:\2005_SSMS.regsrvr")

RepeatTab = 1
Tab = " "

a.Writeline ("<?xml version=""1.0"" encoding=""utf-8""?>")
a.Writeline ("<Export serverType=""8c91a03d-f9b4-46c0-a305-b5dcc79ff907"">")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerType id=""8c91a03d-f9b4-46c0-a305-b5dcc79ff907"" name=""Database Engine"">")
a.Writeline (RepeatString(Tab,RepeatTab) & "<Group name=""SQL Locator Import"" description=""Servers Located by SQL Locator and Imported into SSMS"">")

RepeatTab = RepeatTab +1

For i = 1 To objSQLList.Count
servername = objSQLList.Item(i)

a.Writeline (RepeatString(Tab,RepeatTab) & "<Server name=""" & servername & """ description="""">")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<ConnectionInformation>")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907<⁄ServerType>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ServerName>" & servername & "<⁄ServerName>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<AuthenticationType>0<⁄AuthenticationType>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<UserName ⁄>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<Password ⁄>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<AdvancedOptions>")
RepeatTab = RepeatTab +1
a.Writeline (RepeatString(Tab,RepeatTab) & "<PACKET_SIZE>4096<⁄PACKET_SIZE>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<CONNECTION_TIMEOUT>15<⁄CONNECTION_TIMEOUT>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<EXEC_TIMEOUT>0<⁄EXEC_TIMEOUT>")
a.Writeline (RepeatString(Tab,RepeatTab) & "<ENCRYPT_CONNECTION>False<⁄ENCRYPT_CONNECTION>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄AdvancedOptions>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄ConnectionInformation>")
RepeatTab = RepeatTab -1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Server>")

Next
RepeatTab = 2
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Group>")
RepeatTab = 1
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄ServerType>")
RepeatTab = 0
a.Writeline (RepeatString(Tab,RepeatTab) & "<⁄Export>")


Set Args = wScript.Arguments

If Args.Count = 0 then
msgbox "Registry Import File is ready", vbInformation+vbOKonly
end if

a.Close()

Set a = Nothing
Set objSQLList = Nothing
Set objSQLDeeMOApp = Nothing

Function RepeatString(strInput, intCount)
Dim arrTmp()
ReDim arrTmp(intCount)
RepeatString = Join(arrTmp, strInput)
End Function

wscript.quit


Simply copy and paste the above code into a .vbs file and run it. The output will be in your root C directory.

No comments:

Post a Comment