Hi,
I'm writing some scripts at the minute to archive data from one database to another.
in order to make sure that trasactions are correct (whatever is deleted is transferred to the other database) and that no errors occur I need to check @@error after each TSQL statement (insert into .... delete from .....)
the script i have runs fine and i can see in query analyser that the number of rows deleted matches the number of rows inserted. however i'd like to also use @@rowcount to rollback the transaction if the number of rows deleted does not match the number of rows inserted
the problem is that @@error is reset after each statement and the same with @@rowcount
so checking the @@rowcount value resets the @@error value
basically i'm looking to do the following (pseudo)
insert into table2 select * from table1 where dateif @@error<>0 rollback transactions
delete from table1 where dateif @@error<>0 rollback transactions
if @inscount<>@delcount rollback transaction
i was thinking about doing the following after each del or insert statement
insert into #temptable select @@error,@@rowcount
although i'm not sure if this would work corretly or if anyone knows a better way
Cheers