Registering multiple SQL Server instances
-
Hi All,
Recently moved to a new position where we currently have 180 SQL Servers. To register these in E.M. manually would be very time consuming and I can only think this can be done automatically via SQL-DMO.
I can get the list of SQL Server instances into a table or text file.
I've never used SQL-DMO before and although it would be useful to learn just don't have the time at the moment !
If I'm correct that SQL-DMO is the easiest way forward does anyone have a script that can do this. Would be very grateful.
All versions are SQL Server 7.0 and have VB6 available.
Regards,
Paul W.
Paul R Williams.
-
The Following VB 6.0 Code seems to satisfy your Need. I have tested it on VB6.0 with SQL2000 DMO. It should work with SQL7 DMO Also . Check it out and confirm it to me.
Private Sub cmdRegisterServers_Click()
Dim server As New SQLDMO.SQLServer
Dim app As SQLDMO.Application
Dim grps As SQLDMO.ServerGroups
Dim grp As SQLDMO.ServerGroup
Dim regsrvs As SQLDMO.RegisteredServers
Dim regsrv As SQLDMO.RegisteredServer
Dim ServerName As String
ServerName = "SERVER2"
'The 3 lines connect
server.LoginSecure = False
server.Name = ServerName
server.CONNECT , "sa"
Set app = server.Application
Set grps = app.ServerGroups
Set grp = grps.Item(1)
Set regsrvs = grp.RegisteredServers
'The following Loop gives you the List of Registered Servers.
For i = 1 To regsrvs.COUNT
Set regsrv = regsrvs.Item(i)
Debug.Print regsrv.Name
Next i
Dim NewServers(0 To 2) As String
NewServers(0) = "ram"
NewServers(1) = "avinash"
NewServers(2) = "server1"
'The Following Loop Registers new SQL Server
For i = LBound(NewServers) To UBound(NewServers)
Set regsrv = New RegisteredServer
regsrv.Name = NewServers(i)
regsrv.LOGIN = "sa"
regsrv.password = ""
regsrvs.Add regsrv
'The above line Throws an error if the
'
Concerned Server is already Registered'"Err.number=-2147201023 "
'"Err.Description=[SQL-DMO]This object is already in a collection."
Next i
End Sub
Avinash Barnwal (avin_barnwal@yahoo.co.uk)
-
Avinash
Thanks for reply.
On further investigation on what I was doing it became apparant that I could use SQL-DMO a lot more so decided that I really needed to get to grips with it. Spent all day yesterday going through BOL.
Used your script as a reference and also articles on this site about SQL-DMO to gain some understanding.
I prefer VBScript as I use that more in DTS so I wrote it in VBScript and it worked fine, but you're script was a good starting point. Thanks.
Paul R Williams.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply