Distributor cannot connect to subscriber

I am setting up my 2005 Replication system...

publisher = 2005 sp1

Subscriber = 2005 sp1

I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.

Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.

I am not sure where to look at next... I never had this issue in 2000.

Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.

I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.

Could it be that my pub/dist is using the wrong client files?

|||

Hi William,

Are you using merge or transactional replication?

Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.

In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?

Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.

This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hey Tom,

I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.

The subscription is a push subscription. I will take a look at the link you sent.

=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.

Is there anything I can do to ensure it is working?

|||

update

Since my subscriber is a dev server and if I mess it up it does not matter I did the following...

I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.

No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...

Any idea? or is this a Bug?

|||

Can you try the following connectivity test from command prompt?

at your dev machine, try to connect to the other box

osql -S<publisher_server> -U<user id> -P<password>

and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.

Gary

|||

Ok... I did as suggested and the publisher has no problem connecting to the subscriber...

So what does that mean?

I can connect via command line,SMS but not replication.

|||

Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....

So when I execute from 80/tools/binn I can connect without issue.

From 90/tools/binn I get this error

[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]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.

|||

Can you check the protocal interface are enabled for TCP and name pipe?

You can find the instruction on the following posting from Mahesh,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1

Gary

|||

I double checked and I already have that set. I cycled the server anyway... Still no luck.

What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...

Is there anyway to check what libraries each client is using?

|||

What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

OS is Windows 2003 SP1...

I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...

I

|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||

That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...

So I am asking my boss for other options...

Thanks for all the help.

distributed transaction issue

Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
Jeff
There are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>

distributed transaction issue

Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
JeffThere are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>