Distributed transaction with clustering server -- sql server clustering

Yuval Peleg
11/23/2004 8:03:30 AM

I can't perform a distributed transaction between a remote
server to the central server.

I have 3 sql server 2000 enterprise edition. 2 of them are
installed on win2K server (LOCAL1, LOCAL2) and the other
(CENTRAL_SQL) is installed on clustering win2K server.
None of them is in domain.

Linked servers are defined well between them and I can
perform select statement from any server to any other
server.

I can also perform a distributed transaction between the
remote servers, from LOCAL1 to LOCAL2 and contrarily:

set xact_abort on
begin distributed tran
select count(*) from
LOCAL1.tempdb.INFORMATION_SCHEMA.TABLES
commit
set xact_abort off

MSDTC service starts up with local system account or other
administrators belonging account.

I can also ping the local servers from the central and
even manage them using the Active Directory.

When I perform a distributed transaction between a remote
server (LOCAL1 or LOCAL2) and the central server I get an
error:

Server: Msg 7391, Level 16, State 1, Line 5
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

What am I doing wrong?

Thanks,
Yuval Peleg
DBA, Nice Systems

Alfred XYZ
11/29/2004 1:37:04 AM

I also got the same issue in my company's system.
My alternative option is to remove a pair of
"begin/commit tran" statements.
I tried without "begin/commit trans" to fail joined-tables
update from two linked servers, and it can prevent
asynchronized transaction on that table update, and thus
preserve transactional consistency. Maybe
that "begin/commit tran" has been implicitly enclosed!
Does anyone know why?
Regards,
Alfred XYZ

[quoted text, click to view]

Yuval Peleg
11/30/2004 6:31:14 PM

We solved the problem by organizing the DNS addresses at all servers:

The clustering server is our Domain Controller, so it has to contain the
addresses of all participant servers: itself physical nodes, its virtual
address and the remote SQL server addresses.

At the local servers we signed, in TCP/IP properties, the first node of the
clustering server as the "preferred DNS server" and the second node the
"alternate DNS server".

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clustering

sql server clustering : Distributed transaction with clustering server