How to programmatically add and remove server registration in SQL 2000?

  • 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

  • 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]

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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...

  • 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]

  • 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...

  • 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]

  • i need RegisteredServers class code...... plz help me

  • 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