Replication Error

Hi all,
I am trying to configure a Transactional replication using remote distribution and pull subsriber.

I was able to successfull configure distribution db.Then when i tried to configure Publication database i get the error.


"Cannot promote the transaction to the distributed transaction because there is an active savepoint in this transaction . Changed database context to 'EMRS'(Microsoft sqlserver error.3933)

How could i resolve the problem.

Thanks
emamuthu

Did you see any open transactions exists on the server using DBCC OPENTRAN?

Mohammed U.

yes..i did..there is nothing open..there is no wait for status also if u ran sp_who2

emamuthu

If you get the downtime, try after server reboot.

Mohammed U.

i tried restarting the sqlserver also...i still get the error...
i am able to do transaction replication with push subscriber with dist in the same server as pubs.

emamuthu

Try using local distributor...

Try creating the publication after disabling the REMOTE CONNECTIONS configuration in SAC.
After creating publication enable REMOTE CONNECTIONS...




Mohammed U.

i am able to create local distributor ..that works fine..only the local pub with remote dist fails.

We have 2 servers ,sqlserver 2005 on windows 2003

Server 1 - Pub ,server 2- Dist & Sub

Steps i follow to create to repl are

1. Created the dist (Server 2 - Dist) - No problem. It creates the dist_admin userid with sysadmin rights
2. Creats a linked servers repl_distributor with dist admin link
3. Now i go to server 1 and configure the pub
I get the error
"Cannot promote the transaction to the distributed transaction because there is an active savepoint in this transaction . Changed database context to 'EMRS'(Microsoft sqlserver error.3933)"

Is anything wrong on my side.

Thanks
emamuthu

Problem solved.
In server configuration option 'remote proc trans' was turned on. Went & turned off the option. It works.

Thanks
emamuthu

Interesting...good to know it is working...

Did you enable it after creating publication?
Is replication working without any issues?

Mohammed U.

I didn't enable it again...i don't think i need that...microsoft is going to remove that option .
As of now it working without any problem

Emamuthu

emamuthu,

Thanks for the update...


Mohammed U.

Mohammed

Incase we find open transactions, what are we supposed to do?

Thanks
Satya

It depends what the error is and what you are doing?
In this case you may need to cleanup(kill) any open transactions (orphaned)...

You will get the SPID in the output of DBCC OPENTRAN...


MohammedU.
Moderator
SQL-Server-Performance.com