Tracking Error 266 in SQL Server 2005

Question

Is there a way to trap error 266 inside JDBC or inside SQL by wrapping the exec statement somehow?

We strive to develop security around stored procedures that exit with a transaction that is open in the usual way. This helps prevent a programmer error that can cause significant problems in conjunction with the connection pool.

Answer 1

Yes. Use SET XACT_ABORT ON.

It suppresses error 266 and also results in rollback under any circumstances, including client team timeouts that simply break.

, PerformanceDBA, .

:

• SO proc .
• Erland Sommarskog ( , )
• SET XACT_ABORT ON

Answer 2

• , 15000 IIRC, , . , , proc.

• , SQL . - "" , , . ( ) . . ; 1205. , spid.

• , , . , , . (, ) - .

• . ( ), , . ( 40 ):

  • .
  • , .
  • ,
  • DML
  • 
    ,

• , ( ):

  • ( , )
  • , ,
    .
    , , , , , ; . , . , , , - , , , . ,

• , , spid (, , ), . ANSI SQL 89 ( 92). . , ( ), "", SQL, , ; .

  , NO AUTOCOMMIT CHAINED, ANSI SQL. , () MS ​​ , () , .

• , , , , ( , ), , , , , .

• , , try/catch/finally, , , , try/catch . SQL-, , , . SQL - , , .

  , , () , , () , .

  , ...