Tuesday, March 27, 2012

Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!

Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...

> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob

No comments:

Post a Comment