@@TRANCOUNT incremented by SET NOCOUNT ON

[hmckillop]

Why does the @@TRANCOUNT get incremented by the
SET NOCOUNT ON statement.
I have noticed this problem intermitently and it can cause headaches for concurrency problems, so I just wanted to know if anyone knew why this happens?

e.g.


CREATE PROCEDURE usp_mig_SPName
(
@po_SuccessFailureFlag bit OUTPUT
)

AS

SET NOCOUNT ON

DECLARE @v_InTransaction INT
SET @po_SuccessFailureFlag = 1 --initially set to true


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--Obtain the number of active transactions for the current connection
SET @v_InTransaction=@@TRANCOUNT
IF @v_InTransaction=0
BEGIN
BEGIN TRANSACTION
--NEVER GETS IN HERE AS THE TRANCOUNT IS INCREMENTED BY THE NOCOUNT STATMENT - Shown clearly in debuging
END




--Release the read locks
IF @v_InTransaction=0
BEGIN
COMMIT TRANSACTION
END

 

[JamesLean]

I don't think it's the SET NOCOUNT statement which is causing a transaction. Simple test script:

SELECT @@TRANCOUNT AS before

SET NOCOUNT ON

SELECT @@TRANCOUNT AS after

Even if you

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

you should find this returns 0 both times.

--James

 

[hmckillop]

James

Thanks but the set nocount on definitely is incrementing the @@Trancount. In only happens now and again, and we can see the @@Trancount incrementing by 1 as we execute the SET NOCOUNT ON statement.
I thought maybe it was a problem which would be fixed by a service release, or would be on a forum, but no luck so far.

Thanks anyway.

 

[billchris]

Are you using ADO to execute the procedure? If so, are you calling .BeginTrans on the ADO connection? That would cause @@TRANCOUNT to be 1.

If usp_mig_SPName is being called from another procedure that is beginning a transaction, you would see the same result.

Those are the places I would look at first. Sorry if they are obvious.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal