asked on

MS DTC Crashing SQL out

Hi,

I have a stored procedure that when I run sometimes crashes out SQl to the point where I need to reboot the server. The stored procedure runs a number of delete SQL statments from 5 tables in my database and then deletes a line out of a table on my AS400 system.

The client im running it form freezes and any open instances of enterprise manager lookiung at the server has to be end processed as they hang.

I get the following event log messages

ID : 53305
The MS DTC XA Transaction Manager could not enroll with the MS DTC Transaction Manager on behalf of the XA resource manager. DSN=cwbcore.dll File=d:\nt\com\complus\dtc\dtc\xatm\src\xasynch.cpp Line=3561.

ID : 17052
Error: 17883, Severity: 1, State: 0
Process 89:0 (147c) UMS Context 0x068A6E48 appears to be non-yielding on Scheduler 0

The SQl server is on Service pack 4. It seems to be a problem with reagrds to DTC as the 2 stored procedures that i have amanged to screw my server with all do something with AS400 tables.

Any help would be GREATLY appreciated.

Antony

James Murrell

have you re install service pack 4?

gardmanIT

ASKER

I havent reinstalled it.. should i?

btw this is on a SQl cluster..

gardmanIT

ASKER

Also full version info of my SQl server

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

James Murrell

also take a look at http://support.microsoft.com/kb/223397

gardmanIT

ASKER

i dont have any of the errors mentioned in teh above link.

James Murrell

i would reinstall service pack 4

James Murrell

http://support.microsoft.com/kb/891268 talks about non-yielding on Scheduler 0

gardmanIT

ASKER

have already looked at this and it says that this bug was fixed in Sp4 :(

James Murrell

so looks like reinstall service pack 4

gardmanIT

ASKER

I will schedule sp4 install in for sometimes over the weeknd..

Question that i jsut thought about.. do you think that BEGIN TRAN COMMIT TRAN might cause problems when being used with this bit of the attached code : -

SET @TSQL = 'DELETE FROM Openquery(GMP01A, '+
              '''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DO WHERE DONE_BTCH = ' + @BATCHNO + ' '+
              ''') WHERE DONE_BTCH = ('+@BATCHNO+')'
      EXEC (@TSQL)

CREATE  PROCEDURE dbo.prc_batch_remove_reprocess (@BATCHNO nvarchar(30))
AS
SET XACT_ABORT ON
SET NOCOUNT ON
DECLARE @TSQL nvarchar(300)
 
 
BEGIN TRAN
	SET @TSQL = 'DELETE FROM Openquery(GMP01A, '+
		    '''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DO WHERE DONE_BTCH = ' + @BATCHNO + ' '+
		    ''') WHERE DONE_BTCH = ('+@BATCHNO+')'
	EXEC (@TSQL)
	DELETE FROM [GLOAD].[DBO].[TBL_LOG] WHERE LOG_BATCH = @BATCHNO--  AND LOG_TYPE = 1
	DELETE FROM [GLOAD].[DBO].[tbl_OHeader] WHERE BATCH_NUMBER = @BATCHNO
	DELETE FROM [GLOAD].[DBO].[tbl_OLine] WHERE BATCH_NUMBER = @BATCHNO
	DELETE FROM [GLOAD].[DBO].[tbl_OText] WHERE BATCH_NO = @BATCHNO
	DELETE FROM [GLOAD].[DBO].[tbl_CText] WHERE BATCH_NO = @BATCHNO
	DELETE FROM [GLOAD].[DBO].[tbl_Email] WHERE EMAIL_ARG = @BATCHNO
COMMIT TRAN
GO

Open in new window

ASKER CERTIFIED SOLUTION

gardmanIT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial