Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

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

Aug 19 2010 4:58AM by Madhivanan   

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

Now execute the vb code, the result is 2

Remove transaction part in VB

Private Sub Form_Load()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
con.Open "connection string"
Set rs = con.Execute("exec get_count")
MsgBox rs("trancount")
End Sub

Now execute the vb code, the result is 1

It means that if there is no transaction in a stored procedure, the transaction activated from the application is not considered by @@TRANCOUNT. But if there is a transaction in a procedure, it consider both the transactions (from application as well as procedure). So it is better to make note of this behaviour

Tags: t-sql, sql_server, BRH, #TSQL, TRANCOUNT, VB.NET, VB6, #DOTNET,


Madhivanan
3 · 40% · 12862
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hello! Very useful article. Thank-you. I believe the reason behind this behaviour is that @@TRANCOUNT returns the number of active transactions with respect to the current transaction context. Since we did not explicitly start a transaction within the stored procedure, it returned 0. In the second case, the transaction in the stored procedure probably acts as a nested transaction (easiest test: simply issue a ROLLBACK TRAN in the stored procedure - VB should end up in an error when trying to commit the outer transaction). In the third case, we only have one active transaction.

    commented on Aug 21 2010 12:18PM
    Nakul Vachhrajani
    4 · 36% · 11533
  • Thanks Nakul. That was my point too

    commented on Aug 24 2010 4:01AM
    Madhivanan
    3 · 40% · 12862

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Understanding the behavior of @@TRANCOUNT when querying from a client application" rated 5 out of 5 by 1 readers
SQL Server - Understanding the behavior of @@TRANCOUNT when querying from a client application , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]