November 3, 2008 at 9:51 am
I need to programmatically add and delete server registration using, if possible, C#.
I have the code written using SMO. Management and the RegisteredServer class. It t works for SQL 2005, servers are added and removed, RegSrvr.xml is created in
…. Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml. SQL Management Studio correctly reads the changes made by the program.
The same code runs without problems on the machine where SQL 2000 is installed, creating the same RegSrvr.xml file. However the Enterprise Manager does not see the modification, it does not read RegSrvr.xml file.
Any help will be appreciated.
Dana
November 3, 2008 at 11:08 am
Yes, that is correct. You have to change it in the registry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 11:39 am
Here is the VB.net code that I use to do something similar (discussed at http://www.sqlservercentral.com/Forums/FindPost494720.aspx):
Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Server
Imports system.Data
Module Util
Public Sub Servers_List(ByVal lstOut As IList)
Dim rss As RegisteredServers.RegisteredServerCollection = SmoApplication.SqlServerRegistrations.RegisteredServers
Dim rse() As RegisteredServers.RegisteredServer = SmoApplication.SqlServerRegistrations.EnumRegisteredServers
Dim rsgs As RegisteredServers.ServerGroupCollection = SmoApplication.SqlServerRegistrations.ServerGroups
Dim sg As New RegisteredServers.ServerGroup("AutoRegistered")
'sg.Parent = rss.Parent
If Not rsgs.Contains("AutoRegistered") Then
sg.Create()
Else
sg = rsgs.Item("AutoRegistered")
End If
lstOut.Clear()
For Each rs As RegisteredServers.RegisteredServer In rse
lstOut.Add(rs.Name.ToUpper)
Next
Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
'If dr!Instance IsNot DBNull.Value Then
' lstOut.Add(dr!Name & "\" & dr!Instance)
'Else
If Not lstOut.Contains((dr!Name).ToString.ToUpper) Then
lstOut.Add(dr!Name.ToString.ToUpper)
Dim rs As New RegisteredServers.RegisteredServer(sg, dr!Name)
rs.ServerInstance = dr!Name
rs.Create()
End If
Next
End If
End Sub
End Class
(edit: added vb formatting tag. Nice!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 12:43 pm
Thank you for a quick replay about changing the registry …
I now have c# code that updates the registry, however, I have no idea, what encoding to use for REG_BINARY data in order for my Enterprise Manager to read it correctly
Here is the task my code needs to do for SQL 2000:
1)delete server registrations, if they exist
2)add new server registration using “SQL server authentication”
Thanks Dana
November 4, 2008 at 1:23 pm
You can do this with the SMO RegisteredServer class:
Dim rs As New RegisteredServers.RegisteredServer(sg, dr!Name)
rs.ServerInstance = dr!Name
rs.LoginSecure = False 'means: SQL Server Login
rs.Login = "MySqlLogin"
Dim pwd As New System.Security.SecureString
For Each c As Char In "MyPassWord"
pwd.AppendChar(c)
Next
rs.SecurePassword = pwd
rs.Create()
(edit: added vb formatting tag. Nice!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 1:39 pm
As explained in my first post, I have similar code in c#, works perfectly for SQL2005, but not SQL 2000.
(more precisely: the xml file is changed, but not the entry in the registry. It seems that the Management studio (for SQL 2005) works with the xml file, while Enterprise Manager (SQL 2000) works with Windows registry.
So, as suggested in your first replay, I now modify registry entries directly, but I have the encoding issue.
Hopefully, I am not totally off ...
Thanks
November 4, 2008 at 2:41 pm
Dana (11/4/2008)
As explained in my first post, I have similar code in c#, works perfectly for SQL2005, but not SQL 2000.(more precisely: the xml file is changed, but not the entry in the registry. It seems that the Management studio (for SQL 2005) works with the xml file, while Enterprise Manager (SQL 2000) works with Windows registry.
Are you saying that you were using SMO and had this problem? I thought that you were writing to the XML file yourself and that SMO would correctly maintain both locations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 5, 2008 at 6:18 am
Yes, I have code that uses SMO and it runs ... (it is quite similar to yours, only it is in C#). That code however changes xml file, but not Windows registry.
SQL Server 2005 Management Studio Express communicates with xml file (…. Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml), so there is no problem
SQL Server 2000 Enterprise Manager, however, does not. It reads from Windows registry instead...
November 5, 2008 at 7:39 am
In that case you might try DMO as well.
As for the BINARY thing, I wouldn't know, the last time I wrote directly to the Registry was years ago and in VB6. Not sure how to do binary data type parameters from .Net. And I really don't know how you are going to do the password encryption/decryption without SMO/DMO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 5, 2008 at 8:01 am
Thanks.
I started to look at SQLDMO already, but was hoping I can avoid it ...(Will I end up with the same "encoding issue" at the end?)
in .Net it is easy to write binary to registry, using Registry and RegistryKey
(i.e. regkey.SetValue("dana",bytes, RegistryValueKind.Binary));
I "just" do not know the right encoding...
November 5, 2008 at 8:38 am
I have not used DMO, but it is was SQL server 2000 Enterprise Manager uses and it does appear somewhat similar in approach to SMO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 12, 2009 at 7:25 am
i need RegisteredServers class code...... plz help me
February 12, 2009 at 10:32 am
I have already posted 2 examples in this thread. If they are not what you need, then you will have to be more specific.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy