BEGIN TRAN increments @@TRANCOUNT to 2

I have a problem with the below code that seems to open 2 transactions(why
not just one) - what am I doing wrong?
Regards,
Janusz
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)Hi,
The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
was opened for Begin Tran.
If you run the following code you will find the same, I am not sure what is
your requirement here.
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
COMMIT --This is for the the imlicit transaction on
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
o/p
After commiting trans. Opened trans::1
After commiting trans. Opened trans::0
Vishal Khajuria
SUNGARD SCT INDIA
"rejki" wrote:

> I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
>|||See SET IMPLICIT_TRANSACTIONS in BOL.
It has a perfect example showing the variation in @.@.TRANCOUNT
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"rejki" <rejki@.discussions.microsoft.com> wrote in message
news:DE9B1D00-8D58-4F10-9614-866EC64BF1E5@.microsoft.com...
>I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' +
> convert(varchar,@.@.TRANCOUNT)
>|||Thanks for reply,
If you add print statement after "SET IMPLICIT_TRANSACTIONS ON" you will see
that it does not open transaction. @.@.TRANCOUNT goes to 2 after "BEGIN TRAN"
statement.
The problem I am having is that I do only one commit, but when I execute my
SQL script (similar to one I posted) SQL Analyzer thinks that I have still
uncommitted transaction and when I try to exit SQL Analyzer it asks me
whether I want to close open transaction.
I could add one more “commit” but I would prefer to understand what is g
oing
on there.
Regards,
Janusz
"Vishal Khajuria" wrote:
> Hi,
> The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
> was opened for Begin Tran.
> If you run the following code you will find the same, I am not sure what i
s
> your requirement here.
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> COMMIT --This is for the the imlicit transaction on
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> o/p
> After commiting trans. Opened trans::1
> After commiting trans. Opened trans::0
>
> Vishal Khajuria
> SUNGARD SCT INDIA
>
> "rejki" wrote:
>|||Hi,
What happens with IMPLICIT_TRANSACTIONS is this:
If there's already a transaction in progress (say at a higher scope in a cal
ling stored procedure or some such), nothing else happens; you simply join t
hat transaction.
If, however, there's no current transaction in progress, then executing any
DML/DDL statement will start a new transaction. In this case, all you need t
o do is COMMIT or ROLLBACK. A better approach even than this is to use XACT_
ABORT, which will ensure that any failures below 21 rollback the current tra
nsaction scope. That way, you can raise a suitable error instead of just rol
ling back (or worse, writing extra code to rollback and raise an error!).
The following demonstrates...
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
SELECT|||Sorry, here's the complete demonstration
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
UPDATE table1
SET field1 = NULL
WHERE field2 = 'some value'
IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)
BEGIN
RAISERROR (N'Failed to update fact table.', 16, 1)
END
-- if we got this far then we're good!
COMMIT TRANSACTION
GO|||One other thing, by way of explanation about the following
bit of SQL:

IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)

This is testing some condition to see if the operation was
successful. You may not care about the outcome, in which case you
can just COMMIT, but given that you're in a transaction in the
first place, I'd guess you're going to want to test some sort
of condition to see if this really worked out, before deciding
whether or not to commit your changes.
Cheers,
Tim