Row count in temp table

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  June 16, 2011 at 12:22 pm

  #1339963

  Nested transactions

  http://msdn.microsoft.com/en-us/library/ms189336.aspx

  Jayanth Kurup[/url]

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  June 16, 2011 at 12:30 pm

  #1339975

  GilaMonster (6/16/2011)

  ---

  ...Not what I was thinking about at all. For savepoints, the name of the transaction is still meaningless, the name of the savepoint is what's important.

  Agreed. There is an important distinction in terminology that I missed that is not present in the application of the technique and I used them interchangeably.

  Transaction/Savepoint names are a huge source of confusion. The syntax allows what follows a ROLLBACK TRAN to be either a savepoint_name or a transaction_name...and it can't be determined based only on the rollback statement alone.

  ROLLBACK { TRAN | TRANSACTION }

  [ transaction_name | @tran_name_variable

  | savepoint_name | @savepoint_variable ]

  [ ; ]

  Providing a "transaction name" in the BEGIN TRAN statement does not have an effect on the transaction boundary in a SAVE TRAN scenario as you demoed. I'll have to adjust my terminology.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  June 17, 2011 at 6:48 am

  #1340367

  Sachin4all (6/13/2011)

  ---

  Begin Transaction Tran7

  insert into #t (...)

  SELECT ..

  FROM #hs t

  INNER JOIN table1 on

  If @@ERROR <> 0

  Begin

  Rollback Transaction Tran7

  INSERT INTO LogTable Values (7,@@ROWCOUNT,'F',@Startdate,getdate())

  End

  Else

  Begin

  Commit transaction Tran7

  INSERT INTO LogTable Values (7,@@ROWCOUNT,'S',@Startdate,getdate())

  end

  Can you tell me how to rollback the transaction if it fails?

  This is a VERY common misunderstanding. You MUST store BOTH variables IMMEDIATELY after the DML, thusly:

  DO SOME DML

  SELECT @rowcount = @@ROWCOUNT, @err = @@ERROR

  IF...

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service