Just another tip I got during inspecting a query written for a SSIS package. ie from the same incident which I explained in my last post about SQL. There was some part of SQL stored procedure which is not supposed to run in transaction. Since we don’t have transaction suppression support in SQL Server, the alternative took was as follows.
- Find out whether the stored procedure is running in transaction.
- If so throw error. Since this SSIS package is supposed to be called from multiple places we cannot assume that it will not be called without transaction.
How to check for presence of transaction inside SQL SPIts simple as checking the return value of XACT_STATE() function anywhere in SQL.The code snippet is given below for reference.
Putting it in a SP.
Real time applicationOne of the real time use I could see is to simulate the .net model transaction suppression as mentioned below inside any stored procedure.
A link I obtained which explains about the unavailability of transaction suppression in SQL Server and how to tackle the situation by alternatives.