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()
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
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.ConnectionEnd Sub
Dim rs As New ADODB.Recordset
con.Open "connection string"
con.BeginTrans
Set rs = con.Execute("exec get_count")
MsgBox rs("trancount")
con.CommitTrans
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