Transaction Problen

[ryand09]

I've got the procedure below that inserts data from 2 tables into 2 history tables. I want to put the INSERT statements into a transaction but it doesn't seem to be rolling back if there is a problem ? Also is there any way I can get the procedure to send an email indicating if the transaction has succeeded or failed ?

Thanks in Advance.


CREATE PROCEDURE dbo.TestArchive
(@Date1 datetime,@Date2 datetime)
AS
BEGIN

SET NOCOUNT ON

BEGIN TRANSACTION

--Archive 1
Insert into a_tbl1(afld1, afld2, afld3)
Select (fld1, fld2, fld3) from tbl1
WHERE (((iDATE) Between '' + @Date1 + '' And ''+ @Date2 + ''))

IF @@error <> 0 begin
rollback tran
return
END

--Archive 2
Insert into a_tbl2(afld4, afld5, afld6)
Select (fld4, fld5, fld6) from tbl2
WHERE (((iDATE) Between '' + @Date1 + '' And ''+ @Date2 + ''))


IF @@error <> 0 begin
rollback tran
return
END

COMMIT TRANSACTION

SET NOCOUNT OFF
END

Thanks in Advance.

[skhanal]

Depending on the type of error sql server terminates the batch, in that case you can't get to rollback statement.

To send an email you can setup sql mail or you can use smtp mail server.

http://support.microsoft.com/default...b;en-us;312839