SQL Server Performance Forum – Threads Archive
Transaction Error
Hi :After executing the procedure I get this error in UPDATE PROD1.ATT.DBO.JDE_PTSISSUE STATEMENT as Server: Msg 8525, Level 16, State 1, Procedure JDE_SP_SparePartsIssue, Line 92
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
————- ALTER PROCEDURE JDE_SP_SparePartsIssue
AS
DECLARE @SEEDVAL INT
DECLARE @MAINKEY1VARCHAR(10)
DECLARE @JVNUMBER1 INT
DECLARE @FOREVERCTR1DECIMAL
DECLARE @TICKET_NOVARCHAR(20)
DECLARE @MAINKEY2VARCHAR(10)
DECLARE @JVNUMBER2 INT
DECLARE @FOREVERCTR2DECIMAL
DECLARE @RESETCTRSMALLINT
DECLARE @FOREVERMAX DECIMAL
[email protected]
DECLARE @ROWSINT
[email protected]
DECLARE @MSGVARCHAR(30) IF NOT EXISTS ( SELECT * FROM OPENQUERY(PROD1,’SELECT * FROM PROD1.ATT.DBO.JDE_PTSISSUE WHERE READYFLAG= ”Y” AND JDEFLAG=”N”’))
RETURN IF NOT EXISTS(SELECT * FROM JDEKEYS WHERE MAINKEY = ‘GL0701’)
BEGIN
SET @MSG = ‘MAINKEY NOT FOUND. PHASE 1 IN JDE_SP_SPAREPARTSISSUE’
–GOTO ERRHANDLER1
RETURN
END
ELSE
BEGIN
SELECT @JVNUMBER1 = CASE JVNUMBER WHEN 0 THEN 1 ELSE JVNUMBER+1 END FROM JDEKEYS WHERE MAINKEY=’GL0701′
END SET @MAINKEY1 = ‘GL0701’ IF EXISTS(SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE ‘#JDEFLEETTEMP%’)
BEGIN
DELETE FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE ‘#JDEFLLETTEMP%’
TRUNCATE TABLE #JDEFLEETTEMP
END SELECT @FOREVERMAX = MAX(FOREVERCTR) FROM JDEKEYS
SET @FOREVERMAX = @FOREVERMAX + 1
SELECT TREDAC,TRLNTY,IDENTITY(INT,1,1) AS TREDTN,TREDST1,TREDTN1,TREDLN,TREDDT,TREDMT,TRDGJ,TRMCU,TRDCT,TRCRCD,TRACR,TREXA,TREXR,TRSBL,TRUNIT,TRASID,TRU,TRUM,TREDSP INTO #JDEFLEETTEMP FROM F55T01Z WHERE 1 = 2
DBCC CHECKIDENT(#JDEFLEETTEMP,RESEED,@FOREVERMAX) SET @[email protected]@ERROR IF @ERR<> 0
BEGIN
SET @MSG = ‘ERROR OCCURED AT PHASE 3 IN JDE_SP_SPAREPARTSISSUE’
GOTO ERRHANDLER
END IF EXISTS(SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE ‘#RESETCTR%’)
DELETE FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE ‘#RESETCTR%’
BEGIN TRANSACTION JDESPAREPARTSTRN
SELECT ROWID,IDENTITY(INT,1,1) AS RESETCTR, ISSUE_DATE,AMOUNT,SIDENO,SYSTIMESTAMP INTO #RESETCTR FROM OPENQUERY(PROD1,’SELECT * FROM PROD1.ATT.DBO.JDE_PTSISSUE WHERE READYFLAG= ”Y” AND JDEFLAG=”N”’)
INSERT #JDEFLEETTEMP(TREDAC,TRLNTY,TREDST1,TREDTN1,TREDLN,TREDDT,TREDMT,TRDGJ,TRMCU,TRDCT,TRCRCD,TRACR,TREXA,TREXR,TRASID,TREDSP)
SELECT ‘GL’,’07’,’01’,@JVNUMBER1,RESETCTR, JDEINTERFACE.DBO.GETJULIANDATE(SYSTIMESTAMP),JDEINTERFACE.DBO.GETJDETIMEFORMAT(SYSTIMESTAMP),JDEINTERFACE.DBO.GETJULIANDATE(ISSUE_DATE),’WAITING’,’JM’,’AED’,(AMOUNT*100),’FLEET ANYWHERE INSIDE REPARE’, ‘SPARE PARTS ISSUE’,SIDENO,’N’ FROM #RESETCTR
INSERT F55T01Z (TREDAC,TRLNTY,TREDTN,TREDST1,TREDTN1,TREDLN,TREDDT,TREDMT,TRDGJ,TRMCU,TRDCT,TRCRCD,TRACR,TREXA,TREXR,TRASID,TREDSP)
SELECT TREDAC,TRLNTY,TREDTN,TREDST1,TREDTN1,TREDLN,TREDDT,TREDMT,TRDGJ,TRMCU,TRDCT,TRCRCD,TRACR,TREXA,TREXR,TRASID,TREDSP FROM #JDEFLEETTEMP SELECT @SEEDVAL = MAX(TREDTN) FROM #JDEFLEETTEMP UPDATE JDEKEYS
SET JVNUMBER = @JVNUMBER1,
FOREVERCTR = @SEEDVAL WHERE MAINKEY = ‘GL0701’
BEGIN DISTRIBUTED TRANSACTION FLEETTRN
UPDATE PROD1.ATT.DBO.JDE_PTSISSUE
SET JDEFLAG =’Y’
FROM #RESETCTR A INNER JOIN PROD1.ATT.DBO.JDE_PTSISSUE B ON A.ROWID = B.ROWID
AND READYFLAG=’Y’ AND JDEFLAG=’N’ SET @ERR = @@ERROR
IF @ERR <> 0
BEGIN
ROLLBACK TRANSACTION FLEETTRN
RETURN
END
ELSE
COMMIT TRANSACTION FLEETTRN
TRUNCATE TABLE #JDEFLEETTEMP
TRUNCATE TABLE #RESETCTR SET @ERR = @@ERROR IF @ERR<> 0
BEGIN
SET @MSG = ‘ERROR OCCURED AT PHASE 4 IN JDE_SP_SPAREPARTSISSUE’
GOTO ERRHANDLER
END
COMMIT TRANSACTION JDESPAREPARTSTRN
RETURN
ERRHANDLER1:
EXEC MASTER.DBO.XP_LOGEVENT 70000,@MSG,ERROR
RETURN ERRHANDLER:
EXEC MASTER.DBO.XP_LOGEVENT 70000,@MSG,ERROR
ROLLBACK TRANSACTION JDESPAREPARTSTRN
RETURN
GO
———-
I tried removing the BEGIN DISTRIBUTED TRANSACTION but no luck, then I tried pasting just the update part in another demo sp and there it works fine. I am sure it has to do something with the TRANSACTION. Somebody please help me. Thanks
– Marjo.
I remember reading that, this error occurs due to a timeout. You are using stored procedures, make sure your transaction does not time out or re-write code to divide transaction into managable transactions in bits. If infinite timeout does not help you I can remember some other possible reasons for this.
a)Does system work on one application or many applications involved?
b)Do you have any triggers making ROLLBACK TRANSACTION or COMMIT TRANSACTION?
c) Don’t use Named Pipes anywhere at all as communication protocol for MSDTC or SQL Server. Use TCP/IP stack only. Make sure all machines defaulted to use TCP/IP. And all of the above make sure MSDTC is started without any error, check SQL error log. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
As mentioned it could be a timeout error or due to an unreliable network connection. We get this error quite often on a distributed transfer job over an unreliable connection. We set to job to retry twice if it fails. /Argyle
I changed Prod1 [test server having Sql Server 7 and win nt] to PRODUCTIONSRV which is our production server having sql server 2000 and wind 2000, the sp ran without the above mentioned error. Still I haven’t tried out the connection timeout and other options as suggested by Argyle and Satya – [ thanks for the suggestions]. But now I am facing a new problem, I have scheduled this sp to run every 15 min. Till ystdy the job was running 90% correctly and 10% was getting failed. when I checked the job history 2dy, Its the other way round I mean out of 6 attempts only 1 attempt is successful. I have found the error that is causing the sp to fail i.e.
Server: Msg 7391, Level 16, State 1, Procedure JDE_SP_SPAREPARTSISSUE, Line 58
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]. In this trouble shooting article, microsoft says that this error is related to win 2003 enterprise edition and sql 2000 but I am using wind 2000 and sql 2000. Any suggesstions as to Y most of the time the job is failing ? Thanks
– Marjo.
Oops, I 4got 2 give the link of microsofthttp://support.microsoft.com/default.aspx?scid=kb;en-us;329332
Any firewall is involved in the network and between SQL servers? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I just asked our SA abt the firewall, he said its only when somebody from outside is trying to access our servers. But between both the sql servers there is no firewall and both the sql servers are on the same network. Thanks
-Marjo.
Fine, have you enabled Network DTC Access for MSDTC? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Name resolution must work between the two servers and the MSDTC service must be running on both servers to mention a few things. Verify DNS settins etc. See this post for more infi:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1531
]]>