December 10, 2015 at 11:08 am
Hi,
I get the following error after executing the procedure .
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
ALTER PROCEDURE [usp_GetCommitmentCurvesWithStatus]
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON;
SET ANSI_WARNINGS OFF;
BEGIN TRY
BEGIN TRANSACTION
-- Replace hardcoded dates with Solman Dates
DECLARE @MinDateDATE,
@MaxDateDATE
CREATE TABLE #MYDATA
(
FirstDayWeekDATETIME
,Obj_idVARCHAR(255)
,SolmancategoryVARCHAR(200)
,SolmanStatusTextVARCHAR(255)
,SolmanPercentageCompleteFLOAT
,RowNumINT DEFAULT 0
)
-- Delete Previous Load
TRUNCATE TABLE ABC.CommitmentCurvesWithStatusStatic;
-- Retrieve the Min and Max Values
SELECT@MinDate = Min(DateValue)
FROMvwSolutionManager
SELECT@MaxDate = DATEADD(year, 1, getdate())
FROMvwSolutionManager
-- RETRIEVE PLAN/ACTUAL data CROSS JOINING WITH DATES AND GETTING ALL THE STATUSES
INSERT INTO #MYDATA
SELECT
crosstable.FirstDayWeek
,crosstable.OBJ_ID
,crosstable.SolmanCategory
,sb.CalculatedStatus as SolmanStatus
,sb.SolmanPercentageComplete
,ROW_NUMBER() OVER (PARTITION BY crosstable.OBJ_ID, crosstable.SolmanCategory ORDER BY FirstDayWeek) RowNum
FROM
(
SELECT
weeks.FirstDayWeek
,documents.obj_id
,'Actual' AS 'SolmanCategory'
FROM
[dbo].[udf_GetFirstDayWeekInterval](@MinDate, getdate()) weeks
CROSS JOIN
(
SELECT
DISTINCT obj_id
FROM
ABC.SolutionManager
WHERE
obj_type in
(
SELECT
DISTINCT [SolmanDocumentType]
FROM
[SolutionManagerPercentages]
)
) documents
UNION
SELECT
weeks.FirstDayWeek
,documents.obj_id
,'Plan' as SolmanCategory
FROM
[dbo].[udf_GetFirstDayWeekInterval](@MinDate, @MaxDate) weeks
CROSS JOIN
(
SELECT
DISTINCT obj_id
FROM
ABC.SolutionManager
where
obj_type in
(
SELECT
DISTINCT [SolmanDocumentType]
FROM
[SolutionManagerPercentages]
)
) documents
) crosstable
LEFT JOIN ABC.vwSolutionManager sb ON crosstable.firstdayweek = sb.WeekStart AND crosstable.SolmanCategory = sb.SolmanCategory AND
REPLACE(crosstable.obj_id,' ','') = REPLACE(SB.obj_id,' ','') and sb.ROWNUMBER=1
-- RETRIEVE BASELINE data CROSS JOINING WITH DATES AND GETTING ALL THE STATUSES
INSERT INTO #MYDATA
SELECT
crosstable.FirstDayWeek
,crosstable.OBJ_ID
,crosstable.SolmanCategory
,sb.SolmanStatus
,sb.SolmanPercentageComplete
,ROW_NUMBER() OVER (PARTITION BY crosstable.OBJ_ID, crosstable.SolmanCategory ORDER BY FirstDayWeek) RowNum
FROM
(
SELECT
weeks.FirstDayWeek
,documents.obj_id
,'Baseline' AS 'SolmanCategory'
FROM
[dbo].[udf_GetFirstDayWeekInterval](@MinDate, @MaxDate) weeks
CROSS JOIN
(
SELECT
DISTINCT obj_id
FROM
ABC.SolutionManagerBaseline
WHERE
obj_type IN
(
SELECT
DISTINCT [SolmanDocumentType]
FROM
[SolutionManagerPercentages]
)
) documents
) crosstable
LEFT JOIN ABC.[vwSolutionManagerBaseline] sb ON crosstable.firstdayweek = sb.WeekStart AND crosstable.SolmanCategory = sb.SolmanCategory AND
REPLACE(crosstable.obj_id,' ','') = REPLACE(SB.obj_id,' ','') and sb.ROWNUMBER=1 -- This is used to get only the latest occurence of the date for that week. Ex: Review and Complete happen on the same week, we only get Complete
-- INSERT ALL THE DATA FOR PLAN/ACTUAL filling the gaps with the previous found value and 0 / Not started in case the value is not found
INSERT INTO ABC.CommitmentCurvesWithStatusStatic
SELECT
b.[OBJ_ID]
,b.
,b.[STATUS_TEXT]
,b.[STATUS]
,b.[OBJ_TEXT]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[OBJ_TYPE])),''),'NULL') AS 'OBJ_TYPE'
,b.[OBJ_TYPE_TEXT]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[TEAM])),''),'NULL') AS 'TEAM'
,NULLIF(NULLIF(LTRIM(RTRIM(b.[DevTeam])),''),'NULL') AS 'DEVTEAM'
,CASE(b.OBJ_TYPE)
WHEN 'ZPDD' THEN NULLIF(B.BPCYCLE,'')
ELSE NULLIF(B.BUILDCYCLE,'')
END AS 'BPCYCLE'
,NULLIF(NULLIF(LTRIM(RTRIM(b.[BUILDCYCLE])),''),'NULL')AS 'BUILDCYCLE'
,b.[GLOBAL]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[RELEASE])),''),'NULL') AS 'RELEASE'
,b.[REQID]
,b.[TESTCYCLE]
,b.[USERASSIGNED]
,b.[WRICEFID]
,curr.FirstDayWeek
,curr.Solmancategory
,ISNULL(Curr.SolmanPercentageComplete,ISNULL((
SELECT
TOP 1 curr2.SolmanPercentageComplete
FROM
#MYDATA curr2
WHERE
curr2.rownum < curr.rownum AND curr2.Obj_id = curr.Obj_id AND curr2.Solmancategory = curr.Solmancategory
AND curr2.SolmanPercentageComplete IS NOT NULL
ORDER BY
curr2.rownum DESC
), 0)) AS 'Percentage'
,ISNULL(Curr.SolmanStatusText, ISNULL((
SELECT
TOP 1 curr2.SolmanStatusText
FROM
#MYDATA curr2
WHERE
curr2.rownum < curr.rownum AND curr2.Obj_id = curr.Obj_id AND curr2.Solmancategory = curr.Solmancategory
AND curr2.SolmanPercentageComplete IS NOT NULL
ORDER BY
curr2.rownum DESC
), 'Not Started')) AS 'SolmanStatus'
, curr.RowNum
FROM
#MYDATA Curr
-- USING RAW SolmanData for plan/actual
INNER JOIN [SolutionManager] b ON b.Obj_id = curr.Obj_id
WHERE
Curr.Solmancategory in ('Actual','Plan')
-- INSERT ALL THE DATA FOR BASELINE filling the gaps with the previous found value and 0 / Not started in case the value is not found
INSERT INTO ABC.CommitmentCurvesWithStatusStatic
SELECT
b.[OBJ_ID]
,'' as 'LINK'
,b.[STATUS_TEXT]
,'' [STATUS]
,b.[OBJ_TEXT]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[OBJ_TYPE])),''),'NULL') AS 'OBJ_TYPE'
,b.[OBJ_TYPE_TEXT]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[TEAM])),''),'NULL') AS 'TEAM'
,NULL AS 'DEVTEAM'
,CASE(OBJ_TYPE)
WHEN 'ZPDD' THEN NULLIF(B.BPCYCLE,'')
ELSE NULLIF(B.BUILDCYCLE,'')
END AS 'BPCYCLE'
,NULLIF(NULLIF(LTRIM(RTRIM(b.[BUILDCYCLE])),''),'NULL')AS 'BUILDCYCLE'
,'' [GLOBAL]
,NULLIF(NULLIF(LTRIM(RTRIM(b.[RELEASE])),''),'NULL') AS 'RELEASE'
,'' [REQID]
,'' [TESTCYCLE]
,'' [USERASSIGNED]
,'' [WRICEFID]
,curr.FirstDayWeek
,curr.Solmancategory
,ISNULL(Curr.SolmanPercentageComplete, ISNULL((
SELECT
TOP 1 curr2.SolmanPercentageComplete
FROM
#MYDATA curr2
WHERE
curr2.rownum < curr.rownum AND curr2.Obj_id = curr.Obj_id AND curr2.Solmancategory = curr.Solmancategory
AND curr2.SolmanPercentageComplete IS NOT NULL
ORDER BY
curr2.rownum DESC
), 0)) AS 'Percentage'
,ISNULL(Curr.SolmanStatusText, ISNULL((
SELECT
TOP 1 curr2.SolmanStatusText
FROM
#MYDATA curr2
WHERE
curr2.rownum < curr.rownum AND curr2.Obj_id = curr.Obj_id AND curr2.Solmancategory = curr.Solmancategory
AND curr2.SolmanPercentageComplete IS NOT NULL
ORDER BY
curr2.rownum DESC
), 'Not Started')) AS 'SolmanStatus'
, curr.RowNum
FROM
#MYDATA Curr
-- USING RAW SolmanBaseline for plan/actual
INNER JOIN [SolutionManagerBaseline] b ON b.Obj_id = curr.Obj_id
WHERE
Curr.Solmancategory = 'Baseline'
INSERT INTO ABC.stg_ProcedureLog
(
[stg_ProcedureLog].[DateLog]
,[stg_ProcedureLog].[LogLevel]
,[stg_ProcedureLog].[ProcedureLog]
)
VALUES
(
GETUTCDATE()
,'INFO'
,'Success updating CommitmentCurvesWithStatusStatic info'
)
DROP TABLE #MYDATA
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
N'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION
END
INSERT INTO ABC.stg_ProcedureLog
(
[stg_ProcedureLog].[DateLog]
,[stg_ProcedureLog].[LogLevel]
,[stg_ProcedureLog].[ProcedureLog]
,[stg_ProcedureLog].[MessageError]
)
VALUES
(
CURRENT_TIMESTAMP
,'ERROR'
,'Error Inserting data into CommitmentCurvesWithStatusStatic'
,ERROR_MESSAGE()
)
END CATCH
RETURN 0
END
GO
Thanks,
PSB
December 10, 2015 at 11:09 am
Also I get
The transaction log for database 'ITT' is full due to 'ACTIVE_TRANSACTION'.
December 10, 2015 at 12:10 pm
You're commiting your transaction twice. Once in the try and once in the catch. Remove one of them to correct the issue.
For the second post, you have an open transaction somewhere. Rollback or Commit the transaction. It was probably left open when testing before the error handling was implemented.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply