Error in transaction

Hello ;

I get error

"111233;The current transaction has aborted, and any pending changes have been rolled back. Cause: A transaction in a rollback-only state was not explicitly rolled back before a DDL, DML or SELECT statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"

My code is:

alter procedure [dbo].[sp_FA_CONTINUITY_RPT_CURVE]
@Package_Datetime [datetime],
@MinDate [datetime],
@MaxDate [datetime],
@Start_Time [datetime],
@End_Time [datetime],
@Execution_Time [int],
@Source_Records [int],
@Target_Records_UPD [int],
@Target_Records_INS [int],
@Linked_Package_ID [int],
@PkgStatus [varchar](10)
as
Begin
declare @TotalRowsADM int, @TotalRowsADMSTAGING int , @pkgInsStatus varchar(10),@pkgUpdStatus varchar(10),
@PackageIDs int,@Start_T datetime,@End_T datetime, @ExecutionTim int
BEGIN TRANSACTION;
--SAVE TRANSACTION MySavePoint;
set @Start_T = coalesce (@Start_T, Getdate ())
BEGIN TRY
truncate table ADM.[FA_CONTINUITY_RPT_CURVE]
insert into ADM.[FA_CONTINUITY_RPT_CURVE] select * from admstaging.[FA_CONTINUITY_RPT_CURVE]
set @TotalRowsADM = (select count(Row_ID) from ADM.[FA_CONTINUITY_RPT_CURVE])
set @TotalRowsADMSTAGING = (select count(Row_ID) from ADMSTAGING.[FA_CONTINUITY_RPT_CURVE])
if(@TotalRowsADM = @TotalRowsADMSTAGING)
begin
truncate table ADMSTAGING.[FA_CONTINUITY_RPT_CURVE]
set @pkgInsStatus = 'Success';
end
else
begin
set @pkgInsStatus = 'Fail';
end
set @PackageIDs = (select top 1 [PackageID] from [SSIS_PackageLOG] where [Target_Table_Name]='ADMSTAGING.FA_CONTINUITY_RPT_CURVE' and [PkgStatus]='Success')
set @End_T = coalesce (@End_T, Getdate ())
set @ExecutionTim = DATEDIFF(second, @Start_T, @End_T)
--insert into abc values(@ExecutionTim )
if(@TotalRowsADM > 0)
begin
insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],
[Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],
[Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',
@MinDate,@MaxDate,@Start_Time,@End_Time,@TotalRowsADMSTAGING,0,@TotalRowsADM,
'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)
end
else
begin
insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],
[Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],
[Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',
@MinDate,@MaxDate,null,null,@TotalRowsADMSTAGING,0,@TotalRowsADM, 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)
end
--commit
END TRY
BEGIN CATCH
set @pkgInsStatus = 'Fail'
if(@TotalRowsADM > 0)
begin
insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],
[Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],
[Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',
@MinDate,@MaxDate,@Start_Time,@End_Time,@TotalRowsADMSTAGING,0,@TotalRowsADM,
'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)
end
else
begin
insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],
[Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],
[Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',
@MinDate,@MaxDate,null,null,@TotalRowsADMSTAGING,0,@TotalRowsADM, 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)
end
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION --MySavePoint; -- rollback to MySavePoint
END
END CATCH
COMMIT TRANSACTION
END

Please help.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ankit Shukla8y ago
Hello Laxmidhar.

Actually Its working fine without transaction. But when I use transaction, It gives error.
0
Laxmidhar Sahoo8y ago
sir Actual I am not awar about your table structure.
I suggest to execute the procedure in try catch block
0