Post reply

Rename SQL Server

  • October 10, 2006 at 8:38 am

    #75072

    I am receiving the following error when I attempt to rename my SQL server (2000)

    Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 39

    Cannot drop server 'SQLSERVERName' because it is used as a Publisher in replication.

    This server is not used as a Publisher in replication.

    It might have been at one time in the past, however replication is not turned on and there are no other indications that replication is occuring on this server. 

    Thanks for your help

     

  • October 11, 2006 at 1:06 am

    #665141

    I recently changed the computername of a machine running SQL Server 2000. The end efffect of this (amongst probably others that I don't know of) was that all SQL Server Agent jobs could no longer be modified. After a bit of looking around on msdn.microsoft.com I found the solution which consisted of:

    1. setting the computername to its original value

    2. unloading and deleting all the jobs

    3. changing the computername to the desired value

    4. reloading all the jobs

    As suggested, there may be other side effects in chaning the computername. So beware.

  • October 11, 2006 at 5:31 am

    #665180

    You can rename the server, but not the SQL Server Instance.  see KB

    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257716

    SCROLL TO #29

    You may have omitted the sp_dropserver and sp_addserver.

     

  • October 11, 2006 at 7:45 am

    #665213

    Thanks for your help, however I was already aware of these articles and omitted no steps.

    The problem is the stored proc sp_MSrepl_check_server that is run is finding entries in msdb.dbo.MSdistpublishers and msdb.dbo.MSdistributiondbs tables for a non-existent replication database.

    I am assuming that my predecessor used an improper method to remove replication from this server.

    I am going to try removing the entries from the above tables and then will attempt to rename the server this weekend (during maint time)

    Thanks again

  • October 11, 2006 at 7:54 am

    #665217

    Try setting up replication and dropping it again.  I know back in 7.0 I would have to do that to get things cleared up correctly.  Haven't had to in 2000.

    Tom

  • October 11, 2006 at 8:03 am

    #665221

    Have you tried running the stored procedures for removing replication?

    http://support.microsoft.com/kb/324401

    this will be cleaner than deleting the entries from the

    "dbo.MSpublications" table, since there are other entries involved. 

  • October 11, 2006 at 8:14 am

    #665224

    Thanks for the quick response.

    Running sp_helpdistpublisher I get no results

    Running sp_get_distributor I get installed=0, no distribution

    running sp_helpdistributor I get null results

    My main problem is  I don't know what database replication was set up for in the past.

    There are no publications listed anywhere in the system. 

    Also this SQL server has been moved to another Windows 2003 Server so I do not have the original hardware either.

    Is there any way I can identify which database was previously used in replication? or the publication name?

    Thanks

     

     

     

     

  • October 11, 2006 at 8:24 am

    #665229

    use

    distribution

    go

    select

    * from dbo.MSpublications

  • October 11, 2006 at 8:26 am

    #665230

    There is no distribution database on the server.

     

  • October 11, 2006 at 8:27 am

    #665231

    I spoke too soon.

    The distribution database was detached

    I have reattached it and am running the query now.

    thanks

  • October 11, 2006 at 8:28 am

    #665233

    Ok after running

    use distribution

    go

    select

    * from dbo.MSpublications

    I get no rows returned.

     

  • October 11, 2006 at 11:51 am

    #665278

    I tried to set up replication on the server in order to then disable it to see if this would resolve my issue.

    I received an error when I clicked Finish on the replication, however now there are no entries in msdb.dbo.MSdistpublishers  or msdb.dbo.MSdistributiondbs

    I hope this has cleared my problem.  I cannot attempt to rename the server until this weekend.

    Thanks everyone for your help

     

  • October 18, 2006 at 7:24 am

    #666484

    I wanted to let everyone know that I was able to rename the SQL server this past weekend.

    By attempting to set up replication on the SQL 2000 server, I was able to clear the erroneous replication settings.  This allowed me to successrully rename the server and all of my related issues are now resolved.

    Thanks again for your great help!

     

  • October 25, 2009 at 12:51 am

    #1070425

    I had the same problem after renaming my server, I couldn't drop it... the solution was to rename it back, drop all existing Replications, Maintenance Plans and Jobs, then you can name it what ever you want with being able to use SP_DropServer and SP_AddServer

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply