Help Using @@error and @@rowcount together

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  November 29, 2005 at 3:31 am

  #90936

  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

  MVDBA

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 29, 2005 at 4:21 am

  #606370

  SELECT @ErrorNo = @@Error, @inscount = @@Rowcount

  Than do whatever checks you need.

  _____________
  Code for TallyGenerator