We help IT Professionals succeed at work.

'SQLOLEDB' was unable to begin a distributed transaction.

ms3756
ms3756 asked
on
Hi,

I m running the distributed update query between 2 servers.
I m running the following query at the Query Analyser at my office's servers which both of the servers are at the same domain. It's works fine.

When I tried to run the same query at my customer's servers, which the 2 servers are at the diffirent domains, I get the following error msg:-

-----------------------------------------------------BEGIN DISTRIBUTED TRANSACTION
UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'
UPDATE remote.pubs.dbo.authors SET au_lname = 'McDonald' WHERE au_id
= '409-56-7008'
COMMIT TRAN
GO
------------------------------------------------------

msg 7391, level 16, state 1, line 2
the operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider return message: New Transaction cannot enlist in the
specified transaction coordinator.]

for ur information, both of the servers have:

(1) MS DTC started
(2) Linked server added
(3) user's permission to access the particular database is given.

what could be the problem ?
Please help. Thanks ..



regs,
ms3756
Comment
Watch Question

Commented:
I think this is something to do with the network domain connectivity, maybe pls check against the NT domain trust.

Author

Commented:
hi,

For ur information, the 2 servers are ob Win2K platform.

if there are no trust relationship to be setup between the 2 servers, can i still perform the BEGIN DISTRIBUTED TRANSACTION ?


regs,
ms3756

Commented:
Is there a Begin...End that shows the begin and end of the transaction?

Author

Commented:
nope.. just the above 4 lines..

Commented:
Would u like to try adding the Begin...End?
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
gigsvoo, there is a BEGIN DISTRIBUTED TRANSACTION and a COMMIT TRANSACTION, plus 2 UPDATE lines in the code...

Author

Commented:
Hi,

I checked it out from someone already..
here you go:>

You cannot execute transactions across domains without proper trust settings in place unless you're running standalone security mode which is NOT recommended. Doesn't make much sense to want to execute commands across domains if you don't trust each other. There are several workarounds to getting this to work but all invovled granting of permission to a "foreign" user at some level.

When you use BEGIN DISTRIBUTED TRAN, it goes through MSDTC which means it will attempt to execute the query on the remote server(s) via MSDTC's login on your local machine. The default setup for MSDTC uses a local system account to login which does not give it any permissions or privileges outside of that specific machine. However, if you don't use BEGIN DISTRIBUTED TRAN, the query executes under the MSSQLServer security context. A lof of users setup that service to login as an administrator or a domain account (sometimes even domain admins) which is why it works. Check out the login accounts for the various SQL Server services and MSDTC. It's likely you'll find the answer there.


That's mean if i need to use the BEGIN DISTRIBURED TRAN, I need to hv trust relation set between the 2 servers in the different domain.


thanks for the help .. :>

regs,
ms3756
SECOND REQUEST, YEAR 2000 QUESTIONS STILL OPEN... *** ADMINISTRATION WILL BE CONTACTING YOU SHORTLY ***

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.11516818.html
http://www.experts-exchange.com/questions/Q.20137653.html
http://www.experts-exchange.com/questions/Q.11710518.html
http://www.experts-exchange.com/questions/Q.12022840.html
http://www.experts-exchange.com/questions/Q.20161420.html
http://www.experts-exchange.com/questions/Q.20178726.html
http://www.experts-exchange.com/questions/Q.20206126.html
http://www.experts-exchange.com/questions/Q.20270601.html


PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.

Commented:
I can see if you had linked the servers properly?
Points refunded and question closed by
Netminder
CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.