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

Conditional Deletion in Replication

I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.

conditional (partial) merge replication?

Hi there,
I hadn't heard of Merge Replication more than a week ago, so my head's
spinning a bit. It sounds like it is what I'll need, but I'm wondering
if things can be conditionally replicated...
The program I'll be writing is for people at forest fires to keep track of
expenses related to their fire. So I would want them to be able to connect
to the central database, do a merge replication to get all of the
information entered by other people about this fire, and then disconnect
their network cable and head off to the fire. When they return to the
office, I would want them to plug in and update the server again with all of
the information they've generated since then and also get any updates from
other users, etc.
What I do not want to do is have the entire central database downloaded to
them -- just the relevant fire (or two or three)
Is this sort of thing possible automatically? I imagine it could be forced
if need be... but it would be great if it's an easy-to-do thing.
Thanks,
Beverley
Beverley,
have a look at dynamic filters for this; Dynamic Horizontal Filters can be
used to partition data according to the subscriber and rely on the use of
the SUSER_SNAME() and HOST_NAME() functions. Also, in the case of
HOST_NAME() there is a parameter for the replication merge agent -
HOSTNAME - this can be set to be different from the computername, and so
could be a business-related value.
HTH,
Paul Ibison