TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Ankit Shukla
NA
681
117.7k
Error in transaction
Dec 18 2017 4:30 AM
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
Reply
Answers (
2
)
SQL update large table column values
MS Access Or MySql