Hi, Pros,
I am new to SQL server 2005. We have installed SQL server 2005 cluster. I tried to setup peer-to-peer replication with another standard server. Unfortunately, when I creating publication, I got error message : SQL server cound not configure "MyServer" as a Distributor [New publication Wizard] Additional information: An exception occurred whild executing a Transact_SQL statement or batch.
The Server "Myserveris already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisitingDistributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to 'master'. [Microsoft SQL Server, Error: 14099]
I tried to use sqlcmd and I got another error message:
C:\>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Does anyone have any idea how to get the sqlcmd work?
Thank you in advance.
Hi Vicent,
You should use "sqlcmd -S %VirtualInstanceName%" (replace %VirtualInstanceName% with the virtual server name of your cluster.
For the issue of setting up distributor, have you follow the instruction in your error message?
Peng
|||Hi, Peng,
Thanks for your kind reply. I am so glad your command works. I got another error when I tried to drop distributor from my cluster because when I setup replication the message shows the cluster has been setup as distributor.
I use sqlcmd to drop distributor and get the error message below. Do you have any idea?
C:\>sqlcmd -S myvirtualname
1> sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>
Thanks for your help.
|||Hi Vincent,
The first parameter of sp_dropdistributor is not server name. Please see this page (http://msdn2.microsoft.com/en-us/library/ms173516.aspx) for the document. For example, you should say "EXEC sp_dropdistributor" or "EXEC sp_dropdistributor 1".
Peng
|||Hi, Peng,
I tried the command EXEC, it does not work either.
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\HCLLCLUS>sqlcmd -S myvirtualname
1> EXEC sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>
Do you know how to fix? Do I need to log on machine instead of virtual name? I wonder how did this distributor setup. Is there any way I can check this?
Thanks,
|||I need to mention one more thing. I did follow the error message and tried to find Disable Publication and Distributor Wizard. It was not there. It seems the only way left for me is the command line.
Thanks.
|||It is really my bad. I use the command to drop off distributor.
1> EXEC sp_dropdistributor
2> GO
Msg 21043, Level 16, State 1, Server virtualserver, Procedure sp_dropdistributor,
Line 50
The Distributor is not installed.
1>
It is looks like the ditributor did not installed on the server. However if I tried to configure replication via wizard, the error message shows the distributor was installed.
SQL Server could not configure ‘virtualname’ as a Distributor.
Additional information.
An exception occurred while executing a Transact-SQL statement or batch.
[Microsoft.SqlServer.ConnectionInfo]
The server ‘Virtualname’ is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the existing Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to ‘master’. [Microsoft SQL Server Error: 14099]
|||
Could you run the following command against your cluster instance and report the result back?
SELECT srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0
If you get any row back, it means that your server is already defined as distributor.
Peng
|||Hi, Peng,
I am glad to tell you. I have resolved this problem. Actually, there was a security problem when I set up replication. I went to Local Security Settings >User Rights Assignment >Lock pages in memory, I put adminstrator user in it and I was able to setup replication. Thanks for your instruction.
I have an another problem. I did not realize that it need SQL Server 2005 Enterprise edition to setup P2P replication. I had bought upraged license. However, I do not know how to upgrade SQL server 2005 standard to Enterprise edition. Does anyone know how? Thank you.
|||Run setup from the command prompt. Here are instructions on how to do this.http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade
