Configure Distribution Wizard - unable to connect to server

I am been trying to setup replication on my local Developer edition copy of SQL Server 2005 SP1, but I keep running to into this unable to connect to server error within MS SQL Server Management Studio environment. I tried various connection settings under the configuration manager and things like adding entries to my host file with no luck. Understand that this requires the server name but failing at providing this for the connection to start the wizard. I am missing something simple here?

Darryl

what is the full error? Is this a remote distributor, and is this a machine in the same domain? If so, make sure to enable remote connections in the SAC.|||

Sorry that I didn't post the full error details on the first post. This is now posted below.

The SQL Server is running locally on the same PC in which I am using MS SQL Server Management Studio. The PC is part of a domain and I am logged using a domain account of which I am the admin on PC. I am able to connect/ open query windows using my domain windows account or SQL Server sa account. This the error below is when to 'right click' on Replication folder and select Configure Distribution. The wizard screen does not appear, just the error. No replication is setup on this server, but the Compact connections tools (IIS setup) were run on this same PC.

Darryl

===================================

SQL Server is unable to connect to server 'DUTTONDARRYL1'. (Configure Distribution Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ConfigureWizardErrorSR&EvtID=CantConnect&LinkId=20476

===================================

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, 'INITIALSETUP1'. (Replication.Utilities)

Program Location:

at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.PrepareToShow()

|||

Did you rename your sql server? In the beginning of the error message it says it cannot connect to DUTTONDARRYL1, but in the end it says INITIALSETUP1. I would remove all the hostfile, aliases, etc. you modified/added, and make sure the sql server has the same name as your machine.

|||

Where does this INITIALSETUP1 name come from or where is it stored? It almost seems like something left over from a partial install. There is no INITIALSETUP1 in the host file and there are no aliases.

Darryl

|||I don't know, you tell me. Do select * from sysservers to see if the entries are correct.|||

Thanks for the select stament. Here is the result with the first 3 column

srvid srvsstatus srvname

0 1089 INITIALSETUP1

1 1184 BAMPrimaryImportSrv

2 1089 DUTTONDARRYL1

3 1089 MSSQLSERVER

The last 2 entries I added today through a store procedure (sp_addserver ?) base on reading other threads on this topic. Ok, I now see were this INITIALSETUP1 is getting stored, can I just write an update statement here against the first record or is there a better way to change the name?

Darryl

PS: Thanks for the quick followup

|||

What are the reasons for calling sp_addserver? You shouldn't be doing this unless you want to use linked servers. See topic "sp_addserver" in Books Online. If you do not need INITIALSETUP1 then you should remove it. Same with MSSQLSERVER entry. You should remove them with sp_dropserver.

|||

Ok I found the solution....

I did need to use sp_addserver, but I was using it incorrectly. As per the on-line books on sp_addserver , this is used for defining a remote server or the name of the local instance of SQL Server. The local instance is what is important to bring up the configuration distribution wizard without the error. Here are my steps:

1) Remove all the servers listed from sp_helpserver or 'select * from sysservers' (as per Greg sugguestion) that are not required or needed for linking. In my case, I removed all server by using the sp_ dropserver

2) Add in the local server using sp_addserver with the local parameter (i.e. sp_addserver @.server'DUTTONDARRYL1', @.local='LOCAL')

3) Re-start SQL Server

You can check if the local instance come up properly if the 'select @.@.servername' displays the server name.

Darryl

PS: Thanks Greg Y for helping me work through this problem