Error when trying to setup replication

I am trying to setup replication on my server, but was getting an error that said:

"The Distributor is not available Error 6. Specified SQL Server not found"

So, I did some searching and found a couple things to check and found

sp_helpserver...the server with id of 0 was a remote server, not the local server instance...and

select @.@.servername also returned the name of that remote server!!

I'm assuming this is my problem, but how did it get this way and how do I fix it?

ThanksOk, I fixed the @.@.servername issue with:

use master
go
exec sp_dropserver 'incorrect server name'
go
exec sp_addserver 'servername','local'
go

Restart sql server service.

sp_helpserver now lists the local server as id of 0 and @.@.servername returns the correct server.

HOWEVER, the same error is there for replication?!?!

There is an entry in sp_helpserver for this:

repl_distributor repl_distributor rpc,dist,rpc out,use remote collation 6 NULL 0 0

Not sure if that helps with anybody trying to help me but anyway...|||Can you describe how you setup the Replication? At which step you get the error and how did you prepare your db server?|||Thanks for the help.

I did not do anything except for click on the server instance->Tools->Wizards and then click Replication->Configure Publishing and Distribution Wizard

I'm new to this server. It appears it has been messed with before but I cannot give you the details of that.|||Joe,

I was able to get it working. For those that find this post, here is what I did.

First, using the commands above, make sure your server is iniitally setup correct!

Next, I disabled publishing on the local server. Tools->Replication->Publishing

Next, I then configured the distributor for the local server. Tools->Replication->Configure Publishing, Subscribers and Distribution

Thanks

error when attempting to configure a local distributor

i'm getting the following error message when i attempt to configure my server as a local distributor:

TITLE: Connect to Server

Cannot connect to 9A6375C3046246B.

ADDITIONAL INFORMATION:

Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

|||

MohammedU wrote:

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

i enabled remote connections to be named pipes only and i'm still getting the same error. i'm using a default instance.|||

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

|||

Madhu K Nair wrote:

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

the windows firewall is already disabled. do i still need to create an exception?

|||does anyone from microsoft care to chime in? anyone else?|||

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

|||

Rishi Maini SQL DBA wrote:

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

thanks for replying. the correct query is the following:

use msdb

go

select srvid

from sys.sysservers

where srvname = '9A6375C3046246B'

the query result is: 0

any other ideas? anyone else care to chime in?

|||i still need help with this issue.

thanks in advance|||i'm going to keep bumping this thread until i get a response from someone with microsoft.|||

Duane Douglas wrote:

i'm going to keep bumping this thread until i get a response from someone with microsoft.

|||

Are you trying to setup through UI or sp?

Have you tried to use "sp_adddistributor"?

If this is an urgent matter, please contact customer support, this is a forum.

Gary

error when attempting to configure a local distributor

i'm getting the following error message when i attempt to configure my server as a local distributor:

TITLE: Connect to Server

Cannot connect to 9A6375C3046246B.

ADDITIONAL INFORMATION:

Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

|||

MohammedU wrote:

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

i enabled remote connections to be named pipes only and i'm still getting the same error. i'm using a default instance.|||

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

|||

Madhu K Nair wrote:

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

the windows firewall is already disabled. do i still need to create an exception?

|||does anyone from microsoft care to chime in? anyone else?|||

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

|||

Rishi Maini SQL DBA wrote:

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

thanks for replying. the correct query is the following:

use msdb

go

select srvid

from sys.sysservers

where srvname = '9A6375C3046246B'

the query result is: 0

any other ideas? anyone else care to chime in?

|||i still need help with this issue.

thanks in advance|||i'm going to keep bumping this thread until i get a response from someone with microsoft.|||

Duane Douglas wrote:

i'm going to keep bumping this thread until i get a response from someone with microsoft.

|||

Are you trying to setup through UI or sp?

Have you tried to use "sp_adddistributor"?

If this is an urgent matter, please contact customer support, this is a forum.

Gary

error when attempting to configure a local distributor

i'm getting the following error message when i attempt to configure my server as a local distributor:

TITLE: Connect to Server

Cannot connect to 9A6375C3046246B.

ADDITIONAL INFORMATION:

Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

|||

MohammedU wrote:

Try after enabling named pipes by default it is only TCP/IP...

Is it default instance or named instance? if it is named instance try to use portnumber along with server name.

ex: servername\instancename,portnumber

i enabled remote connections to be named pipes only and i'm still getting the same error. i'm using a default instance.|||

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

|||

Madhu K Nair wrote:

check whether the server is Firewall enabled. You need to create exception on port

refer

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

Madhu

the windows firewall is already disabled. do i still need to create an exception?

|||does anyone from microsoft care to chime in? anyone else?|||

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

|||

Rishi Maini SQL DBA wrote:

Please check if "select @.@.servername" is returning back you the right ServerName (Boxname)?

If not use

-- sp_dropserver 'incorrect servername','droplogins'--

and

-- sp_addserver 'correctservername','local'--

Even if the select @.@.servername is showing the right servername , Query select srvid from sysservers where srvname=’9A6375C3046246B’

If srvid is not = 0 use sp_dropserver and sp_addserver mentioned above (but this time the incorrect servername will be = correctservername )

Note you need to restart service once for changes to be in effect.

Regards

Rishi Maini

thanks for replying. the correct query is the following:

use msdb

go

select srvid

from sys.sysservers

where srvname = '9A6375C3046246B'

the query result is: 0

any other ideas? anyone else care to chime in?

|||i still need help with this issue.

thanks in advance|||i'm going to keep bumping this thread until i get a response from someone with microsoft.|||

Duane Douglas wrote:

i'm going to keep bumping this thread until i get a response from someone with microsoft.

|||

Are you trying to setup through UI or sp?

Have you tried to use "sp_adddistributor"?

If this is an urgent matter, please contact customer support, this is a forum.

Gary

Error Shapshot Pull (Subscriber)

Hello,
I have two server both running SQL 7 SP4 on W2K server SP 4. One SQL server is both the distributor and publishwer and the other one is the subscriber. I published one database on the server (replication type = shapshot) and there were no problems when ru
nning the jobs that this task creates. But when running the pull job on the subscriber, I get the following error:
Could not drop object 'ACCION' because it is referenced by a FOREIGN KEY constraint.
I don't know why this job attemps to drop the table ACCION when all it's supposed to do is to refresh the data. Or is the way it works?
Any help
Regards
Hi,
I was trying to replicate the error that you are facing.
I was able to replicate the error.
What I understand is, if you modify the relationship at the publisher and
after that when the agent tries to apply the changes to the subscriber, it
throws this error.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.