SQL Server Performance Forum – Threads Archive

Cannot drop transactional publication

Hi, Recently I configured and deployed very complicated replication between sql 2005 cluster as a publisher (few databases are published) and sql 2005 enterprise as distributor. All subscriptions are running on the distributor. So far it was perfect. But then I created a new transactional publication with updateable subscribers on the database that already had one transactional publication. Something went wrong and I could not be able to create subscription – got error messages regarding the linked server and distributed transactions. So I decided to remove that publication then and I cannot do that. The following is the error I’ve got: The operation could not be performed because OLEDB provider "SQLNCLI" for linked server "repl_distributor" was unable to begin a distributed transaction (Microsoft SQL Server, Error 7391) Then I tried to drop this publication in T-SQL: exec sp_droparticle @publication = @publication, @article = @article, @force_invalidate_snapshot = 1 And got the following: Msg 3933, Level 16, State 1, Procedure sp_MSrepl_getdistributorinfo, Line 93
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. Did somebody meet something similar? Thank you, Igor

Try changing the server configuration option ‘remote proc trans’.
If it is ON turned off the option using sp_configure procedure.
MohammedU.
Moderator
SQL-Server-Performance.com

Thank you for reply. I tried that option before I created this topic. It didn’t help.
Igor

Is your replication using the remote distributor? You are getting the error when the following code being executing… — invoked on publisher with remote distributor
—
SELECT @proc = @dist_rpcname + N’.master.sys.sp_MSrepl_getdistributorinfo’
EXECUTE @retcode = @proc @distributor = @loc_distributor OUTPUT, –Line93
@distribdb = @loc_distribdb OUTPUT,
@publisher = @publisher,
@local = @local,
@publisher_type = @loc_publishertype OUTPUT,
@publisher_id = @loc_publisherid OUTPUT,
@working_directory = @loc_working_directory OUTPUT,
@version = @loc_version OUTPUT
MohammedU.
Moderator
SQL-Server-Performance.com

Is MS DTC (Distributed Transaction Coordinator) service running? (you will need this switched on) Are the Publisher and Subscirbers members of the same Domain ? (if not you will need to create trust between the domains) Cheers, Zoran

Two possibilites I can think of, MSDTC security is stronger than you expect due to windows sp2 change, see my post here detailing configuration: http://www.sqlwebpedia.com/content/msdtc-troubleshooting
And remotely possible is having a db trigger that causes the 3933 error.
-Chuck Lathrope

Hi, I’m receiving this same (3933) error when trying to add a publication, and I do have a DDL trigger on the publication database which writes to a database on a linked server. I want to be able to do add the publication without disabled the trigger. Is this possible?

I end up disabling the trigger, running the scripted publication add and enabling the trigger all at once. I haven’t found a better solution yet as this is okay for me.
-Chuck

ran into the same issue. I looked at the three services required by SQL (Sql server, Agent and DTC). The first two were running under a domain account (sysadmin) but the DTC was running as a network service. I changed the DTC service to run under the same acount as the SQL Agent and that addressed the issue.

]]>