Log Shipping – NORECOVERY vs. STANDBY mode

As you all might know, one of the downsides of the @@ERROR variable approach  to catch the  error is that we must check the value of this variable after each and every DML /DDL  statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified.

 

BEGIN TRAN
BEGIN TRY
    SELECT  1 / 0 infinite
    COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 

Does the order in which we put BEGIN TRAN..BEGIN TRY matters , let us check

 

BEGIN TRY 
BEGIN TRAN 
    SELECT  1 / 0 infinite
COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 	

Technorati Tags: begin try begin tran,begin tran begin try,sql server 2005,sql server 2008,error handling