Distributed transaction with clustering server

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

>--Original Message--
>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
>.
>
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".
"Alfred XYZ" <alfredxyz@.hotmail.com> wrote in message
news:0ee901c4d5f6$fc1fa560$a501280a@.phx.gbl...[vbcol=seagreen]
>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
> remote
> are
> other

distributed transaction problem on remote server

I am getting a problem in executing this distributed query



CREATE PROCEDURE [sp_ec2fetch] AS



set XACT_ABORT on

begin distributed tran



select * from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=[ip];User ID=[uid];Password=[pass]'

).dml.dbo.act where code like '4010101000'

commit tran

GO



When I run it with a local LAN IP it works fine, but when I use a public ip my analyzer hangs and doesnt give me any output. I am using a dedicated dialup 64kbps connection. I have tried this query without begin distributed trans and commit trans and it worked fine, may be there are extra overheads with transactions, if so let me know



As an alternate I have tried registering server with sp_addlinkedserver and then executing a query with direct server reference, but had the same problem with it.Ensure MSDTC is started on both the servers.

DISTRIBUTED TRANSACTION

Hi,
I have two sql servers with SQL2000 service pack 3 which are linked by the "
Link Server". When i use the "begin tran" (distributed transaction) in store
d procedure, i am getting the following error.
"Server: Msg 8525, Level 16, State 1, Line 1
Distributed transaction completed. Either enlist this session in a new trans
action or the NULL transaction. "
The following article talks about this problem.
http://support.microsoft.com/?kbid=834849
But, both servers are SQL 2000 in my case. Any help?
Thanks,
VijayHi, i'm going this trouble but the versions are different. Server A has Sql
server 2000 enterprise edition and the server B has Sql Server 7.0, both
servers are linked properly and running msdtc. No changes were made to their
configurations. If I run the sentence with Begin distributed tran and commin
distributed tran it end right but using that statement it fails with the
error commented is this post.
Any ideas?
"Vijay" wrote:

> Hi,
>
> I have two sql servers with SQL2000 service pack 3 which are linked by the
"Link Server". When i use the "begin tran" (distributed transaction) in sto
red procedure, i am getting the following error.
>
> "Server: Msg 8525, Level 16, State 1, Line 1
> Distributed transaction completed. Either enlist this session in a new tra
nsaction or the NULL transaction. "
>
> The following article talks about this problem.
> http://support.microsoft.com/?kbid=834849
> But, both servers are SQL 2000 in my case. Any help?
>
> Thanks,
> Vijay
>
>