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

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
(RefIdvarchar(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/default...;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 message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...

> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;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

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/default.aspx?scid=kb;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 message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;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

error 3926 , server failed to resume transaction

In a high load asp.net environment, I am getting an error.

The transation active in this session has been committed or aborted by another session.

Here is the code , I am getting error.

1object returnObject =null;2 SqlTransaction sqlTrans =null;3try4 {5//Getting new SqlConnection6 comm.Connection = GetConnection();7//Opening connection8 OpenConn(comm.Connection);9//beginning transaction10 sqlTrans = comm.Connection.BeginTransaction( IsolationLevel.ReadCommitted );11//setting transaction to SqlCommand object12 comm.Transaction = sqlTrans;13//executing operation14 returnObject = comm.ExecuteScalar();15//trying to commit.16 sqlTrans.Commit();171819 }20catch (SqlException sex)21 {22if (sqlTrans !=null)23 {24 sqlTrans.Rollback();25 }26 }27finally28 {29 comm.Connection.Close();30 }

Could you please explain , am I doing smthg wrong ?

Check out this link: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2041937&SiteID=17

Good luck.

|||

Hi CS4Ever ,

Thanks for the reply, but as far as I understood , Thona just submitted the situation , not a solution and the 3rd reply belongs to me :)

What could I possibly do ? Submit a bug to Microsoft ?

Thanks in advance.

|||

Still no answers ?