2
Reply

Error in transaction

Ankit  Shukla

Ankit Shukla

Dec 18 2017 4:30 AM
166
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:
  1. alter procedure [dbo].[sp_FA_CONTINUITY_RPT_CURVE]  
  2. @Package_Datetime [datetime],  
  3. @MinDate [datetime],  
  4. @MaxDate [datetime],  
  5. @Start_Time [datetime],  
  6. @End_Time [datetime],  
  7. @Execution_Time [int],  
  8. @Source_Records [int],  
  9. @Target_Records_UPD [int],  
  10. @Target_Records_INS [int],  
  11. @Linked_Package_ID [int],  
  12. @PkgStatus [varchar](10)  
  13. as  
  14. Begin  
  15. declare @TotalRowsADM int, @TotalRowsADMSTAGING int , @pkgInsStatus varchar(10),@pkgUpdStatus varchar(10),  
  16. @PackageIDs int,@Start_T datetime,@End_T datetime, @ExecutionTim int  
  17. BEGIN TRANSACTION;  
  18. --SAVE TRANSACTION MySavePoint;  
  19. set @Start_T = coalesce (@Start_T, Getdate ())  
  20. BEGIN TRY  
  21. truncate table ADM.[FA_CONTINUITY_RPT_CURVE]  
  22. insert into ADM.[FA_CONTINUITY_RPT_CURVE] select * from admstaging.[FA_CONTINUITY_RPT_CURVE]  
  23. set @TotalRowsADM = (select count(Row_ID) from ADM.[FA_CONTINUITY_RPT_CURVE])  
  24. set @TotalRowsADMSTAGING = (select count(Row_ID) from ADMSTAGING.[FA_CONTINUITY_RPT_CURVE])  
  25. if(@TotalRowsADM = @TotalRowsADMSTAGING)  
  26. begin  
  27. truncate table ADMSTAGING.[FA_CONTINUITY_RPT_CURVE]  
  28. set @pkgInsStatus = 'Success';  
  29. end  
  30. else  
  31. begin  
  32. set @pkgInsStatus = 'Fail';  
  33. end  
  34. set @PackageIDs = (select top 1 [PackageID] from [SSIS_PackageLOG] where [Target_Table_Name]='ADMSTAGING.FA_CONTINUITY_RPT_CURVE' and [PkgStatus]='Success')  
  35. set @End_T = coalesce (@End_T, Getdate ())  
  36. set @ExecutionTim = DATEDIFF(second, @Start_T, @End_T)  
  37. --insert into abc values(@ExecutionTim )  
  38. if(@TotalRowsADM > 0)  
  39. begin  
  40. insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],  
  41. [Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],  
  42. [Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',  
  43. @MinDate,@MaxDate,@Start_Time,@End_Time,@TotalRowsADMSTAGING,0,@TotalRowsADM,  
  44. 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)  
  45. end  
  46. else  
  47. begin  
  48. insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],  
  49. [Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],  
  50. [Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',  
  51. @MinDate,@MaxDate,null,null,@TotalRowsADMSTAGING,0,@TotalRowsADM, 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)  
  52. end  
  53. --commit  
  54. END TRY  
  55. BEGIN CATCH  
  56. set @pkgInsStatus = 'Fail'  
  57. if(@TotalRowsADM > 0)  
  58. begin  
  59. insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],  
  60. [Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],  
  61. [Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',  
  62. @MinDate,@MaxDate,@Start_Time,@End_Time,@TotalRowsADMSTAGING,0,@TotalRowsADM,  
  63. 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)  
  64. end  
  65. else  
  66. begin  
  67. insert into [SSIS_PackageLOG]([Package_Datetime],[Source_TableName],[Target_Table_Name],[MinDate],[MaxDate],  
  68. [Start_Time],[End_Time],[Source_Records],[Target_Records_UPD],[Target_Records_INS],[Stage_Name],  
  69. [Linked_Package_ID],[PkgStatus],[Execution_Time]) values (@Package_Datetime,'ADMSTAGING.FA_CONTINUITY_RPT_CURVE','ADM.FA_CONTINUITY_RPT_CURVE',  
  70. @MinDate,@MaxDate,null,null,@TotalRowsADMSTAGING,0,@TotalRowsADM, 'Stage2',@PackageIDs,@pkgInsStatus,@ExecutionTim)  
  71. end  
  72. IF (@@TRANCOUNT > 0)  
  73. BEGIN  
  74. ROLLBACK TRANSACTION --MySavePoint; -- rollback to MySavePoint  
  75. END  
  76. END CATCH  
  77. COMMIT TRANSACTION  
  78. END  
Please help.
 
Thanks 
 

Answers (2)