locked
SQL server replication requires the actual server name to make a connection to the server. RRS feed

  • Question

  • i have a virtual machine that contains sql server that contains a replication ... the publisher and subscriber are on this same VM ... i've cloned this production VM for testing purposes, then i dropped the replication (on the testing) then recreate it again, but the replication won't start and when i tried to right click the publication then choose properties, i got the following error: 

     

    SQL server is unable to connect to server 'testing'

    SQL server replication requires the actual server name to make a connection to the server.
    connections through a server alias, IP address, or any other alternate name are not supported Specify the actual server name, 'production' (Replication. Utilities)

     

    I'm sure i've changed the computer name of the new VM to "testing", but why i'm getting this error?! ... and how can i fix it  ?!!

     


    • Edited by Butmah Tuesday, September 20, 2011 6:01 PM
    Tuesday, September 20, 2011 5:55 PM

Answers

All replies

  • Can you do this?

     

    select @@ServerName

     

    Is this the same as what you get when you go to a command prompt and type

    hostname

    ?

    If not do this:

     


    EXEC  Sp_dropserver   @@ServerName

    GO
    EXEC  Sp_addserver 'newservername' ,  'local'

    GO

    Then restart SQL Server Service and try again.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, September 20, 2011 6:55 PM
    Answerer
  • thanks Hilary,

    the result of select @@ServerName is not the same as cmd hostname

    but when i tried to run 

     

    EXEC  Sp_dropserver   @@ServerName,'droplogins'
    GO

    EXEC  Sp_addserver 'testing' ,  'local'
    GO

     

    i got the following error:

     

    Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31

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

    Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74

    The server 'testing' already exists.

     

    i tried to run exec sp_droppublication  @publication ='all' and it was completed successfully, but when i tried to run the above script i'm still getting the same error.

     

    Wednesday, September 21, 2011 3:54 AM
  • You will need to disable this machine as a distributor and then try again with the sp_dropserver command. Right click on the replication folder to do this.
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Butmah Wednesday, September 21, 2011 12:37 PM
    Wednesday, September 21, 2011 10:09 AM
    Answerer
  • Hilary,

    what if the select @@server and C:>Hostname both return the same server name but I still get the error?  We didn't make any name changes.  The one thing we did recently was changing the IP address scheme from static to DHCP for that server.   I connet to that server by name and it is the default instance.  We have no name instance.

    when I am in SQL mgmt studio, right click to check the snapshot status or right click to select properties, i get the same error.  For me to do any publishing, I need to do it with script.  For me to run the snap shot or check status, I will need to do it in Replication monitor or job agent.

    Any thought?

    od


    Ocean Deep

    Friday, March 9, 2012 11:48 PM