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

Begin Date and End Date

Hi all
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please

SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004

I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO