SQL Server - Understanding the behavior of @@TRANCOUNT when querying from a client application

As you know, @@TRANCOUNT is used to inform you about the number of live transactions active at point. If you want to retrieve it's value using an application, you should be careful about the behaviour of the returned value.

Let us consider the procedure

create procedure get_count
as
select @@TRANCOUNT as trancount

Consider the VB code that calls the procedure

Private Sub Form_Load()

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
con.Open "connection string"
con.BeginTrans
Set rs = con.Execute("exec get_count")
MsgBox rs("trancount")
con.CommitTrans

End Sub

The result is 0 eventhough there is transaction activated by application

Alter the procedure like below

alter procedure get_count
as
begin transaction
select @@TRANCOUNT
rollback transaction

Read more: Beyond Relational

Posted via email from .NET Info