NEED URGENT HELP error while excecuting a transaction involving two servers

Question

NEED URGENT HELP error while excecuting a transaction involving two servers

alvaro cordero-368705

Newbie

Points: 3
More actions
September 20, 2006 at 9:44 am

#90495

Hi.

I have developed an application in C# 2005 that connects to an sql server 2000. The application has its own database, but it also has a view that does a simple select from a table that is in another database in another server.
I am trying to do an insert in a two of the tables that are in the application database. I open a transaction, excecute the first insert, then, to do the other insert, I have to extract a value from the view that is in the other server, and insert that value in the other table of the application database.
The sql server returns the following error.

THE OPERATION COULD NOT BE PERFORMED BECAUSE THE OLEDB PROVIDER "SQLOLEDB" WAS UNABLE TO BEGIN A DISTRIBUTED TRANSACTION
I have already checked that the Distibuted Transaction Coordinator of the SQLServer Agent is running on both servers, and they are running.

Can anybody tell me what could be going wrong and how can I fix the problem ?

Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Two questions need to be answered first before I can try to offer some help. First, are you attempting to update any data on the other server where your view is pulling data. Second what is the OS running on the two servers?

alvaro cordero-368705 Newbie Points: 3 More actions · Answer 2

Thanks !

Here are the answers:

1. Nop. I am not updating any data on the other server. I am just consulting the view that gets its data from the database that is in the other server.

2. One server runs windows 2000 server and the other windows 2003 server

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

As you do not update anything on the other server inside the transaction, can you pull the information you need from the other server outside the transaction, then do the updates inside a transaction without access the other server? This would eliminate the need for a distributed transaction to be created.

The other thing you should do is check the settings for MSDTC on both servers. There is a KB articule in the Microsoft Knowledge base regarding the error you report, I just can't remember it off hand. We had similiar issues here between servers, and we fixed it by making changes to the MSDTC security configurations.

On Windows 2003, go to Component Services, Computers, My Computer, Properties. On the MSDTC tab Network DTC Access should be checked, Allow Remote Clients should be checked, Allow Inbound should be checked, and No authentication should be set.

On Windows 2000, as above, except the properties box is different. Default Properties tab, Default Authentication Level should be none.

Let me know if any of this helps.

alvaro cordero-368705 Newbie Points: 3 More actions · Answer 4

Thank you very much !

I thought about the change in the code also... but I wanted to know the possible cause and how to fix it the next time I face this problem.

I would hand the tips you gave me to them, and see how it works.

Thank you very very very much for your quick help. I really really aprecciate it.