SQL Server Error: [8525] Distributed transaction completed.

cook0002

I'm getting this error when synchronising a stored procedure that joins to a table on a linked server (sorry about the subject matter):



The following error message was returned from the SQL Server:

[8525] Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

The following SQL command caused the error:

CREATE PROCEDURE spS_ChessLeague4 (
@Mode int
)AS
BEGIN
/*
Various modes
1 - Shows players who have played 5 or more games with an average of >= 0.5
2 - Shows players who have played less than 5 games or have an average of < 0.5
*/
SET NOCOUNT ON

create table #ChessLeague (UserID char(8), GamesPlayed int, GamesWon int, GamesLost int, GamesInProgress int)

insert into #ChessLeague
select WhiteUserID as UserID,
(select count(*) from games where whiteuserid = g.whiteuserid and statuscode > 2) as GamesPlayed,
(select count(*) from games where whiteuserid = g.whiteuserid and statuscode in (3,7,9)) as GamesWon,
(select count(*) from games where whiteuserid = g.whiteuserid and statuscode in (4,6,8)) as GamesLost,
(select count(*) from games where whiteuserid = g.whiteuserid and statuscode in (0,1,2)) as GamesInProgress
from games g
where whiteuserid = 'robs0003'
group by WhiteUserID

select * from #ChessLeague

insert into #ChessLeague
select BlackUserID as UserID,
(select count(*) from games where blackuserid = g.blackuserid and statuscode > 2) as GamesPlayed,
(select count(*) from games where blackuserid = g.blackuserid and statuscode in (4,6,8)) as GamesWon,
(select count(*) from games where blackuserid = g.blackuserid and statuscode in (3,7,9)) as GamesLost,
(select count(*) from games where blackuserid = g.blackuserid and statuscode in (0,1,2)) as GamesInProgress
from games g
where blackuserid = 'robs0003'
group by BlackUserID

select * from #ChessLeague

IF (@Mode=1)
select C.UserID, SUM(GamesPlayed) AS GamesPlayed, Users_1.Username as Username,
(select SUM(GamesWon) from #ChessLeague D WHERE D.UserID = C.UserID) AS GamesWon,
(select SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost) from #ChessLeague E WHERE E.UserID = C.UserID) AS GamesDrawn,
(select SUM(GamesLost) from #ChessLeague F WHERE F.UserID = C.UserID) AS GamesLost,
(select SUM(GamesInProgress) from #ChessLeague G WHERE G.UserID = C.UserID) AS GamesInProgress,
(select (CONVERT(float,SUM(GamesWon)*2)+(SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost)))/(SUM(GamesPlayed)*2) from #ChessLeague G WHERE G.UserID = C.UserID) AS WinRatio
from #ChessLeague C LEFT OUTER JOIN
[NBSCOL63].[User].dbo.Users Users_1 ON C.UserID = Users_1.UserId
WHERE (SELECT SUM(GamesPlayed) FROM #ChessLeague WHERE UserID = C.UserID) >= 5
AND (select (CONVERT(float,SUM(GamesWon)*2)+(SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost)))/(SUM(GamesPlayed)*2) from #ChessLeague G WHERE G.UserID = C.UserID) >= 0.5
group by C.UserID, Username
ORDER BY WinRatio DESC, GamesWon DESC, GamesLost
ELSE
select C.UserID, SUM(GamesPlayed) AS GamesPlayed, Users_1.Username as Username,
(select SUM(GamesWon) from #ChessLeague D WHERE D.UserID = C.UserID) AS GamesWon,
(select SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost) from #ChessLeague E WHERE E.UserID = C.UserID) AS GamesDrawn,
(select SUM(GamesLost) from #ChessLeague F WHERE F.UserID = C.UserID) AS GamesLost,
(select SUM(GamesInProgress) from #ChessLeague G WHERE G.UserID = C.UserID) AS GamesInProgress,
(select (CONVERT(float,SUM(GamesWon)*2)+(SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost)))/(SUM(GamesPlayed)*2) from #ChessLeague G WHERE G.UserID = C.UserID) AS WinRatio
from #ChessLeague C LEFT OUTER JOIN
[NBSCOL63].[User].dbo.Users Users_1 ON C.UserID = Users_1.UserId
WHERE (SELECT SUM(GamesPlayed) FROM #ChessLeague WHERE UserID = C.UserID) < 5
OR (select (CONVERT(float,SUM(GamesWon)*2)+(SUM(GamesPlayed)-SUM(GamesWon)-SUM(GamesLost)))/(SUM(GamesPlayed)*2) from #ChessLeague G WHERE G.UserID = C.UserID) < 0.5
group by C.UserID, Username
ORDER BY WinRatio DESC, GamesWon DESC, GamesLost
RETURN (0)
END


The following messages were returned from the SQL Server:

[5701] Changed database context to 'Chess'.
[5703] Changed language setting to us_english.
[0] Creating [dbo].[spS_ChessLeague4]

Brian Donahue

Hello,

This seems to be a known problem when you create a linked server between MSSQL 2000 SP3 servers and MSSQL 7 servers. Can you please try the suggestions from Microsoft TechNet and see if that helps? Here is the link to the Microsoft article:

http://support.microsoft.com/?kbid=834849