July 27, 2018 at 9:15 am
Hi! Ok, this error is bugging me I can't figure it out. We have a stored procedure that is running a transaction and it is being called from inside ColdFusion for our application. This procedure gets called for various things. MOST of the time it works no problem. There is one scenario where its getting called from the ColdFusion code that it returns an error "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.".
If I go to the database and manually pass in the variable and run it from SSMS, it works just fine with no error.
So, I'm super confused what the issue is. It works for some situations in the code, but not this particular one and it ALWAYS works when you run it in the DB manually.
Any insight would be super appreciated.
Here is the stored procedure for reference if it helps. It is actually a lot bigger but I removed a lot of the other insert statements and updates. But all the procedure does is do a series of inserts and updates.
ALTER PROCEDURE [dbo].[MY_PROCEDURE] @ISSUE_NUM INT
AS
SET NOCOUNT ON
BEGIN
SET XACT_ABORT ON
DECLARE @ERRORLOGID AS INT
SELECT RIC, MIN(CMC) AS CMC
INTO #TEMP_XWALK_RIC_CMC
FROM XWALK_RIC_CMC
GROUP BY RIC
/*=======================================================================*/
/*UPDATE APPLICATION */
/*=======================================================================*/
BEGIN TRY
BEGIN TRANSACTION
INSERT SILLY_TABLE6 (SSN6, LVL, SSN_FUNCT, SSN_NOMEN, STATUS, APPN, OSD_SEQ_NO, PNO, BA, SUB_ACTIVITY_GROUP, AAO_IND,
P1_LINENO, RIC, UOM, INT_SPR, BSA, IDENT_CODE, SAR_IND, LOGSA_NOMEN, NEW_TREE, BACK5, SSN_BGFY, SSN_TMFY, RETIRE_FLAG, CMC)
SELECT SUBSTRING(SSN_LOGSA,1,6), LEVEL_RQR, SSN_FUNCT, SSN_NOMEN, 'NEW', APPN, OSD_SEQ_NO, PNO, BA,SUB_ACTIVITY_GROUP, UPPER(AAO),
UPPER(P1), RIS.RIC, UNIT_OF_MEASURE, SUBSTRING(I_SPARES,1,6) AS I_SPARES, BSA, CASE IDENT_CODE WHEN 'NA' THEN NULL ELSE IDENT_CODE END AS IDENT_CODE,
UPPER(SAR), SSN_NOMEN, 'NEW',
CASE WHEN SSN_LOGSA = 'TBD' THEN '' ELSE SUBSTRING(SSN_LOGSA,7,2) + C.CMC + SUBSTRING(SSN_LOGSA,10,2) END AS BACK5,
SSN_BGFY, SSN_TMFY,'N', C.CMC
FROM ANOTHER_TABLE RIS, MAIN_TABLE RC, #TEMP_XWALK_RIC_CMC C
WHERE RIS.ISSUE_NUM = RC.ISSUE_NUM
AND RIS.RIC = C.RIC
AND RC.ISSUE_NUM = @ISSUE_NUM
AND SEND_MAIL=1
UPDATE SILLY_TABLE6
SET MAJOR_ELEMENT = RIS.MAJOR_ELEMENT,
AMMO_CAT_CODE = RIS.CAT_CODE
FROM MAIN_TABLE RC, ANOTHER_TABLE RIS, SILLY_TABLE6 DS
WHERE RIS.ISSUE_NUM = RC.ISSUE_NUM
AND SUBSTRING(SSN_LOGSA,1,6) = DS.SSN6
AND RC.ISSUE_NUM = @ISSUE_NUM
AND SEND_MAIL = 1
AND TCODE = 'H'
AND RIS.APPN = 'AMMO'
UPDATE MAIN_TABLE
SET SEND_MAIL = 0
FROM MAIN_TABLE RC, ANOTHER_TABLE RIS
WHERE RC.ISSUE_NUM = RIS.ISSUE_NUM
AND RC.SEND_MAIL = 1
AND RC.ISSUE_NUM = @ISSUE_NUM
AND ((RIC = 'H9A') OR APPN IN ('BMDO','SOF','CBIP'))
/*UPDATE STATUS*/
UPDATE MAIN_TABLE
SET SEND_MAIL=0
WHERE ISSUE_NUM = @ISSUE_NUM
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXECUTE APPLICATION_AUDIT.AUDIT.SP_LOGERROR @ISSUE_NUM,@ErrorLogID = @ErrorLogID OUTPUT
END CATCH
SET XACT_ABORT OFF
SELECT @ISSUE_NUM
END
July 27, 2018 at 3:14 pm
Dumb question probably, but have you confirmed that SP_LOGERROR is capturing the event every time it errors? If so, have you confirmed in the resulting log that @ISSUE_NUM was a valid value each time? I'm wondering if the CF application is sometimes passing a funky/unexpected value for @ISSUE_NUM and causing the error.
July 27, 2018 at 5:45 pm
autoexcrement - Friday, July 27, 2018 3:14 PMDumb question probably, but have you confirmed that SP_LOGERROR is capturing the event every time it errors? If so, have you confirmed in the resulting log that @ISSUE_NUM was a valid value each time? I'm wondering if the CF application is sometimes passing a funky/unexpected value for @ISSUE_NUM and causing the error.
It's likely because @@TRANCOUNT alone won't tell you if you have an uncommittable (aka doomed) transaction. You need to check XACT_STATE() as well.
XACT_STATE (Transact-SQL)Something else resulted in a doomed transaction. And that can be from errors on the Application side. Or other areas. Search doomed transactions and you should be able to find at least several different scenarios. I've seen them before with nested transactions, connection issues, some other odd error with an app.
Sue
July 27, 2018 at 5:55 pm
Thank you Sue! That is super helpful!
July 27, 2018 at 6:04 pm
I'm still unclear if the CATCH block is even executing or if it's successfully firing the logging SP or not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply