Sql Server 2008
First I created the following procedure with "union all" and faced issue
it doesn't rollback all transactions when there is error.
alter procedure [dbo].[Replicate_Employees_Saad_test] as
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
set nocount on
delete from kpi.dbo.employees_test
where dontdelete=0
insert kpi.dbo.employees_test (
Branch
,Card_Code
,Emp_Name
,DeptName
,Designation
,grade
,left_tag
,active
)
select
Branch = 'D53'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfd53_sql.dbo.employee e (nolock)
join payfd53_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfd53_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000
union all
select
Branch = 'A33'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfgrmn_sql.dbo.employee e (nolock)
join payfgrmn_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfgrmn_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END
--Select * from employees_test
I had to change my procedure.
I removed union all and it worked fine.
alter procedure [dbo].[Replicate_Employees_Saad_test] as
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
set nocount on
delete from kpi.dbo.employees_test
where dontdelete=0
insert kpi.dbo.employees_test (
Branch
,Card_Code
,Emp_Name
,DeptName
,Designation
,grade
,left_tag
,active
)
select
Branch = 'D53'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfd53_sql.dbo.employee e (nolock)
join payfd53_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfd53_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000
insert kpi.dbo.employees_test (
Branch
,Card_Code
,Emp_Name
,DeptName
,Designation
,grade
,left_tag
,active
)
select
Branch = 'A33'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfgrmn_sql.dbo.employee e (nolock)
join payfgrmn_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfgrmn_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END
--Select * from employees_test