Out of transaction (explicit)

[diesirae]

-SQL 2000-
Is there any way to do this?

/*Begin*/
BEGIN TRAN

INSERT INTO DBXXX.dbo.Table (Name, LastName)
VALUES ('jjj','aaaa')

BEGIN TRAN LOG_ACTION
INSERT INTO DBXXX.dbo.ProcesLog (ProcId)
VALUES ('IniProc')

COMMIT LOG_ACTION

IF @@ERROR = 0 COMMIT
ELSE ROLLBACK
/*End*/

I need that, if @@ERROR <> 0 the transaction LOG_ACTION to be commited... and nor rolledback...

¿Is there any way to do this? Any command...

Thanks

 

[mrdenny]

No. The inner transaction is still a part of the outer transaction. Because of this, if you roll back the outer transaction, the inner transaction will also roll back.

I would set the Log_Action info into a variable. Then after your transaction has been commited or rolled back you can issue another command to to the Log insert.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[diesirae]

Excelent!!! Always simplicity is the best way... and this is an example of it...
THANKS!!!!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com