Distributed Transaction on Linked Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distributed Transaction on Linked Servers

Author

Topic

aghabagheri
Starting Member

1 Post

Posted - 2004-07-06 : 09:54:25

description of problem:

I have two server as follows:
sql server1: www
sql server2: aghabagheri
two servers are SQL Server 2000 and MS DTC (Distributed transaction coordinator)service is started on both servers.

'aghabagheri' added in Linked servers list of 'www'. There is no
problem in connection.

when I want to issue distributed queries such this:

declare @Error int

begin distributed transaction trnFa_Events

-- delete from remote server (i.e. aghabagheri)
if exists(select top 1 EventID from aghabagheri.BooksDB.dbo.Fa_Events where eventID='111111111111')
begin
DELETE FROM aghabagheri.BooksDB.dbo.Fa_Events
WHERE (EventID = '111111111111')
end

print N'transfering the EVENT profile ...'

-- transfer from 'www' server to 'aghabagheri' server
INSERT INTO aghabagheri.BooksDB.dbo.Fa_Events
SELECT *
FROM Fa_Events
where EventID='111111111111'

set @error=@@Error

if @error= 0
begin

commit tran trnFa_Events
print N'EVENT profile transfered successfully'
end
else
begin
Rollback Tran trnFa_Events
print N'An error occured'

end

the following error occured:
Server: Msg 7391, Level 16, State 1, Line 9
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].

It is necessary to mention that:
this error only occurs in explicit transactions like above situation

How can I get rid of this error message.

Regards,
Ali Aghabagheri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources