to implement Commit / Rollback

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

to implement Commit / Rollback

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-14 : 03:00:35

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:09:51

[code]CREATE procedure RBI_Control_sp
as

set nocount on

begin try
begin transaction
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

commit transaction
end try

begin catch
rollback transaction
end catch[/code]

E 12°55'05.25"
N 56°04'39.16"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-14 : 03:35:21

Hi peso,
Txs for your reply,moreover i need to set a flag in a temp table for a sucess or failure by this i will send mail using xp_sendmail command on daily basis ,can u pls correct the below query..

alter procedure RBI_Control_sp
as
begin
/**********************************************************************************************************
@ProjectName : CORPORATE PROJECT
@FileName : RBI_Control_sp.sql
@Purpose : RBI SCHEDULING
@TablesAccessed :
@Author : Senthil kumar.M
@Date : 26- mar -2008
@Copyright : ©Ramco Systems Limited. 2008. All rights reserved
@ModifiedBy :
@ModifiedOn :
@ModifiedFor :
*************************************************************************************************************/
set nocount on

begin try
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table in ramcovm392(fin_ods)
exec fin_ods..trun_sp

--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

insert into fin_wh..Status_report([object_name],row,[date])
select 'Data Tranfer','SUCCESS',getdate()
commit transaction
end try

begin catch
insert into fin_wh..Status_report([object_name],row,[date])
select 'Data Tranfer','SUCCESS',getdate() rollback transaction
end catch

set nocount off
end

quote:
Originally posted by sent_sara

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:44:16

[code]alter procedure RBI_Control_sp
as
/**********************************************************************************************************
@ProjectName : CORPORATE PROJECT
@FileName : RBI_Control_sp.sql
@Purpose : RBI SCHEDULING
@TablesAccessed :
@Author : Senthil kumar.M
@Date : 26- mar -2008
@Copyright : ©Ramco Systems Limited. 2008. All rights reserved
@ModifiedBy :
@ModifiedOn :
@ModifiedFor :
*************************************************************************************************************/
set nocount on

begin try

begin transaction

--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table in ramcovm392(fin_ods)
exec fin_ods..trun_sp

--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

insert into fin_wh..Status_report([object_name],row,[date])
select 'Data Transfer', 'SUCCESS', getdate()

commit transaction
end try

begin catch
rollback transaction

insert into fin_wh..Status_report([object_name],row,[date])
select 'Data Tranfer', 'Failure', getdate()
end catch[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources