October 3, 2005 at 2:42 pm
I have been trying to create a VBScript to be executed by the WSH in order to automate the registration of 80+ severs worldwide.
I have sucessfully created the server groups, but I am having a hard time getting the servers to register. It crashes with a memory error:
' 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"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' 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
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on this line : dmoServerGroup.RegisteredServers.Add(dmoRegServer)
Anyideas?
Thanks
October 4, 2005 at 7:05 am
Does it register any servers, or does it crash on the first pass?
Try re-declaring the dmoRegServer for each server, and dmoServerGroup for each group.
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
Do Until MyFile.AtEndOfStream
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
.....
Good luck!
--Glenn
October 4, 2005 at 7:16 am
Just to be safe, I would add to Glenn's code, just before the end of the loop, to clear out the dmoRegServer variable with "Set dmoRegServer = Nothing".
Mark
October 4, 2005 at 7:34 am
Thanks everyone...here is the new script. It is still crashing on the first pass.
' 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 = Wscript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
WScript.Echo Err.Description
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt", ForReading, True)
Do Until MyFile.AtEndOfStream
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Set dmoRegServer = Nothing
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
October 4, 2005 at 7:49 am
What is the exact error you are getting?
Can you create trusted connection registrations to those servers individually (through Enterprise Manager)?
--Glenn
October 4, 2005 at 7:57 am
Yes. I can register them individually - all with trusted connections.
The error is: The instruction ar "0x4240460c" referenced memory at......The memory could not be read." This is in a modal dialog box. After I dismiss it, I get an exit code:
Exit code: -1073741819 , C0000005h
October 4, 2005 at 8:44 am
Try this. Instead of:
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
try:
dmoServerGroups("TAMPA").RegisteredServers.Add(dmoRegServer)
Picked this up in an old thread on microsoft.public.windows.server.migration. I copied your script and got it to fail on my machine, but it worked making this change (well, it was a different error, but I assume related to my assumption of what is in your text file.)
Alternatively, I think you may be able to reset the dmoServerGroup object after the .add to the dmoServerGroups. I think the object reference is lost after the add operation. So perhaps this might work after the .add:
Set dmoServerGroup = dmoApp.ServerGroups("TAMPA")Hope this helps.
Mark
October 4, 2005 at 9:03 am
U R Da MAN!!!!
dmoApp.ServerGroups.Item(1).RegisteredServers.Add(dmoRegServer)
Thanks.
October 4, 2005 at 9:30 am
You probably do not want "...ServerGroups.Item(1).RegisteredServers...", since you do not know that "TAMPA" will be the first item in the collection. You can use the string value to reference the item:
...ServerGroups.Item("TAMPA").RegisteredServers...OR
...ServerGroups("TAMPA").RegisteredServers...are better.
Mark
October 4, 2005 at 9:56 am
When the script is complete could you post it ? This would become an invaluable tool for SQL DBAs everywhere due to present EM limitations.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 4, 2005 at 5:16 pm
This is a great idea, but I had to tinker with it. We have remote production servers visible over a VPN only by TCP/IP address, so I had to add the ability to define the Client Network Utility aliases. These servers are outside our Windows domain, so they also require SQL Server logins. I modified it to ignore group and server definitions that already exist ... you might want it to override existing definitions instead.
We don't have as many servers, so I dropped the input file capability, but it shouldn't be hard to restore if you feel you need it.
Option Explicit
Dim dmoApp, dmoServerGroup
On Error Resume Next
Set dmoApp = WScript.CreateObject("SQLDMO.Application")
' Create Production group
CreateGroup dmoApp, "Production", dmoServerGroup
' Check the aliases for PROD1 and PROD2
' Both use the TCP/IP library, default port
CreateAlias "PROD1", "DBMSSOCN,192.168.1.7"
CreateAlias "PROD2", "DBMSSOCN,192.168.1.8"
' Register the production servers
RegisterServer dmoServerGroup, "PROD1", "xxxx", "yyyy"
RegisterServer dmoServerGroup, "PROD2", "xxxx", "yyyy"
' Create Servers group
CreateGroup dmoApp, "Servers", dmoServerGroup
RegisterServer dmoServerGroup, "GROUCHO", "", ""
RegisterServer dmoServerGroup, "HARPO", "", ""
RegisterServer dmoServerGroup, "CHICO", "", ""
' Create Workstations group
CreateGroup dmoApp, "Workstations", dmoServerGroup
RegisterServer dmoServerGroup, "(LOCAL)", "", ""
RegisterServer dmoServerGroup, "MOE", "", ""
RegisterServer dmoServerGroup, "LARRY", "", ""
RegisterServer dmoServerGroup, "CURLY", "", ""
Set dmoServerGroup = Nothing
Set dmoApp = Nothing
Private Sub CreateGroup(ByRef dmoApp, ByRef sGroup, ByRef dmoServerGroup)
On Error Resume Next
Set dmoServerGroup = dmoApp.ServerGroups(sGroup)
If Err.Number Then
Set dmoServerGroup = WScript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = sGroup
dmoApp.ServerGroups.Add dmoServerGroup
End If
End Sub
Private Sub CreateAlias(ByRef sAlias, ByRef sDefinition)
Dim sh
Set Sh = Wscript.CreateObject("WScript.Shell")
sh.RegWrite "HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo\" & sAlias, sDefinition, "REG_SZ"
Set Sh = Nothing
End Sub
Private Sub RegisterServer(ByRef dmoServerGroup, ByRef sServer, ByVal sLogin, ByVal sPassword)
Dim dmoRegServer
On Error Resume Next
Set dmoRegServer = dmoServerGroup.RegisteredServers(sServer)
If Err.Number Then
Set dmoRegServer = WScript.CreateObject("SQLDMO.RegisteredServer")
With dmoRegServer
.Name = sServer
If sLogin = "" Then
.UseTrustedConnection = 1
Else
.UseTrustedConnection = 0
.Login = sLogin
.Password = sPassword
End If
End With
dmoServerGroup.RegisteredServers.Add dmoRegServer
End If
Set dmoRegServer = Nothing
End Sub
October 4, 2005 at 5:25 pm
I will.
I will post it tomorrow from the office.
I am also developing a few more and will post those as well.
October 5, 2005 at 11:11 am
Well, here is the script as it stands.
It works..of course, everyone would modify as needed.
Thanks guys for all your help!
' 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"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' 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
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply