How to read data from remote server inside a transaction?

Hello, everyone:

I have a local transaction,

BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT

View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

It looks like remote server is not available inside the local transaction. How to handle that?

Thanks

ZYT1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
Eg:

SET XACT_ABORT ON
begin distributed tran

insert into sometable select * from remoteserver.database.dbo..foo

commit tran
SET XACT_ABORT OFF


or check this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2|||Hello, Mailler:

Thanks. MSDTC is running. The query you posted doesn't work.

ZYT|||what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?|||Hi, Mailler:

Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

ZYT|||check the link i given in first reply