Updatable subscriptions just won't work (No transaction is active)

[chpicker]

I'm trying to set up transactional replication with updatable subscriptions for the first time using SQL Server 2005. I've never done replication before, so I followed Microsoft's tutorial on how to set up replication. It works fine. What's NOT working is the updates back to the publisher.

The error I get always starts with "Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication." The current error is: "The operation could not be performed because OLE DB provider "SQLNCLI" for linked server REPLLINK_SQL01..." was unable to begin a distributed transaction." This is followed by: OLE DB provider "SQLNCLI" for linked server "REPLLINK_SQL01..." returned message "No transaction is active.". (Microsoft SQL Server, Error: 7391)

This is the latest in a LONG string of error messages that SQL Server has thrown at me when attempting to subscribe to my publication. On each of the previous ones, I did some research using Google searches and was able to find the solution, leading me to the next error message. It's like one of those games you used to play as a kid, each clue leading to yet another clue, the prize always ahead of you but out of reach. I've dealt with things like RPC not being enabled, no link back to the source server available, DTC not having access, etc.

I've hit a brick wall with this one, though. It's so unbelievably generic that Google searches just don't return anything relevant. I've been fighting with this thing for 4 days now.

Can someone give me step-by-step directions for creating a Transactional publication with updatable subscriptions, and then subscribing to it? I am so thoroughly frustrated with Microsoft right now it's ridiculous. The whole project is at a complete standstill until I can get the updates for this replication working.

I should probably emphasize that the replication works perfectly. I can make changes on the source server, open the table on the subscriber and see the changes. I just can't get updates from the subscriber to go back to the publisher.

Ian

 

[mrdenny]

Have you enabled Network DTC access and configured the security on DTC?

Is this Windows 2003?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chpicker]

The answer to both questions is "yes". I enabled everything I could find on the MS DTC security page on both the publisher and the subscriber. I had to do that to fix one of the previous errors I got.

Both servers are running 2003.

 

[mrdenny]

Check to see that DTC is working correctly.

From each machine force a distributed query to pull data from the other machine.

begin distributed transaction
select *
from [i]RemoteServer[/i].master.dbo.sysfiles
commit

Does that give you an error or does it work correctly?

Are any of these servers clustered? What authentication setting did you select for DTC?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chpicker]

Neither of the servers are clustered. I've changed the credentials for DTC to several different things, from the local service to the Network Service to the local Administrator (same password on both machines) to a specifically created user account.

I'll try the code you posted and let you know how it goes.

 

[chpicker]

Ok, it worked going one way, but I can't figure out how to go back the other way.

The 2 databases are:

SQL01
SQL02\SQLEXPRESS

So, I did: SELECT * FROM SQL01.master.dbo.sysfiles

That worked fine. How do I name the other one? I've tried just SQL02, I get the error "could not find server 'SQL02' in sysservers". I tried SQL02\SQLEXPRESS, and I get the error "Incorrect syntax near '\'". There is an entry for it under Linked Servers on SQL01. The name is "SQL02\SQLEXPRESS". It was automatically created by the Create Publication wizard.

 

[mrdenny]

SELECT *
from [SQL02\SQLExpress].master.dbo.sysfiles

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chpicker]

Denny, thanks for the help. We've decided to start over; both servers are being reformatted. We'll install clean, restore the database on the primary from our backup, and try again. I'll let you know if we have trouble again.

 

[mrdenny]

OK, sounds good. A little drastic, but it'll work.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chpicker]

Yeah; it was a test platform to begin with, so nothing was really lost, but this has taken over a week already and I've gotten nowhere.