Nested Transactions

Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?Use SAVE TRAN in you "inner transaction" instead of BEGIN TRAN. That gives y
ou a savepoint to roll
back to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uIP6f6FSGHA.4976@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer
transaction's statements executing?|||You can't. From SQL BOL:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback
to any other name (other than a valid savepoint name) generates an
error. None of the statements executed before the rollback are in fact
rolled back at the time this error occurs. The statements are rolled
back only when the outer transaction is rolled back."|||Hi rmg66
Rollback can only be used to roll back to the outermost begin tran, or to a
savepoint. Note that there really is not concept of nesting transactions in
SQL Server. Please read about transaction control in the Books Online.
begin tran One -- the label here is useless
insert into XX (data) values ('Tran1')
SAVE tran Two -- this does not start a new transaction, it only marks a spot
in the log that we can roll back to
insert into XX (data) values ('Tran2')
rollback tran Two -- roll back to named savepoint
insert into XX (data) values ('Tran1')
Commit tran One -- the label here is useless
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uIP6f6FSGHA.497
6@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?

Nested operators

Hi all
I would like to submit a question to the SQL Server expert group about a
problem that I am having related with nested operators; it may seem somethin
g
foolish to ask but it is driving me crazy since 4 hours ago:
I am using a case statement that looks like this (I put it simplified just
for not being too long):
…..
CASE WHEN Field1 = ‘A’
THEN Field2*12/44
ELSE 0
END
….
The thing is that if I put the second line as shown above, it works well, it
gives me the right result.
But, if I put it as follows:
THEN Field2*(12/44)
It always gives me zero (0) in the result… and I don’t understand WHY
Even though this case can be easily fixed by excluding the parentheses; how
can it be managed in another case where the nesting must be specified?
Any ideas would be appreciated
Thanks in advance,
AnaIn your first example, I'll guess that Field2 is a float or decimal so it's
promoting 12 to decimal/float and then doing the same to 44.
In the second example, because of the parentheses, it's doing the division
first (12/44).
Because both numbers are integers, the result is 0.
Change Field2*(12/44) to Field2*(12.0/44) or cast one of the 2 numbers to
float or decimal and try it again.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:D45BDEBC-C934-4B3F-BCDD-7FD65BE8213B@.microsoft.com...
> Hi all
> I would like to submit a question to the SQL Server expert group about a
> problem that I am having related with nested operators; it may seem
> something
> foolish to ask but it is driving me crazy since 4 hours ago:
> I am using a case statement that looks like this (I put it simplified just
> for not being too long):
> ...
> CASE WHEN Field1 = 'A'
> THEN Field2*12/44
> ELSE 0
> END
> ..
> The thing is that if I put the second line as shown above, it works well,
> it
> gives me the right result.
> But, if I put it as follows:
> THEN Field2*(12/44)
> It always gives me zero (0) in the result. and I don't understand WHY
> Even though this case can be easily fixed by excluding the parentheses;
> how
> can it be managed in another case where the nesting must be specified?
> Any ideas would be appreciated
> Thanks in advance,
> Ana
>|||I believe it is evaluating 12/44 as an integer value. this is around 0.27,
which gets rounded to 0 to make it an integer.
Add in a cast to make this work correctly...
Then cast(field2 as decimal(8,1) * (cast(12 as decimal(8,1)/cast(44 as
decimal(8,1))
Or, alternatively, the following should work, but the conversion is
implicit, not explicit...
THEN Field2*(12.0/44)
Adding the .0 after the number casts it as a decimal, and the calculation
will return a decimal value.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:D45BDEBC-C934-4B3F-BCDD-7FD65BE8213B@.microsoft.com...
> Hi all
> I would like to submit a question to the SQL Server expert group about a
> problem that I am having related with nested operators; it may seem
something
> foolish to ask but it is driving me crazy since 4 hours ago:
> I am using a case statement that looks like this (I put it simplified just
> for not being too long):
> ...
> CASE WHEN Field1 = 'A'
> THEN Field2*12/44
> ELSE 0
> END
> ..
> The thing is that if I put the second line as shown above, it works well,
it
> gives me the right result.
> But, if I put it as follows:
> THEN Field2*(12/44)
> It always gives me zero (0) in the result. and I don't understand WHY
> Even though this case can be easily fixed by excluding the parentheses;
how
> can it be managed in another case where the nesting must be specified?
> Any ideas would be appreciated
> Thanks in advance,
> Ana
>|||My guess it the datatype of the Field2 is INT...
Without the brackets the order of execution leads to implicit
conversion to another more compatible type, whereas the second forces
an INT to be created since you multiply the another number by an INT
explicitly.|||Look at the change in results when you use 12/44 vs. 12.0/44.0
Try not to depend on the implicit data type conversion.|||Raymond, thank you very much.
I applied what you said and it works well now.|||No problem Ana.
Interesting problem.
I've seen many "integer division" questions asked here.
But it's the first time I've seen one like this.
"Ana Morales" <AnaMorales@.discussions.microsoft.com> wrote in message
news:DE51690B-E471-450E-A7F3-FEC2D8AF932D@.microsoft.com...
> Raymond, thank you very much.
> I applied what you said and it works well now.|||Johny D,
Can you please elaborate? When I run the code 12/44 I get 0, but 12.0/44 =
12/44.0 = 12.0/44.0 = .272727.
The implicit conversion seems to return the correct values.
However, I do avoid implicit conversion and use explicit conversion myself,
for two reasons (perhaps this is your point?)
1. The next programmer/DBA to view the code knows that the conversion is
happening.
2. If the rules for implicit conversion ever change, my code will still
work.
Was this your point?
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1147118556.054867.100550@.i39g2000cwa.googlegroups.com...
> Look at the change in results when you use 12/44 vs. 12.0/44.0
> Try not to depend on the implicit data type conversion.
>|||Yes that was exactly my point...
My point was that without the decimal point, SQL Server is assuming the
values are INTEGERs, hence you always end up multiplying your value by
0 since 12/44 as an INT evaluates to 0.
However with the decimal specified, SQL Server then uses a more
appropriate datatype.
My assumption is that FLOAT is used by default when the '.' is
specified. if you prefer to specify the precision and scale you could
define the value as DECIMAL(10,4)
e.g.
DECLARE @.c_factor DECIMAL(10,4)
SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
correct typing
PRINT @.c_factor
DECLARE @.c_factor FLOAT
SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
correct typing
PRINT @.c_factor|||Ahhhh...
I had forgotten that the '.' casts the number to float rather than decimal.
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1147185919.270887.310080@.e56g2000cwe.googlegroups.com...
> Yes that was exactly my point...
>
> My point was that without the decimal point, SQL Server is assuming the
> values are INTEGERs, hence you always end up multiplying your value by
> 0 since 12/44 as an INT evaluates to 0.
> However with the decimal specified, SQL Server then uses a more
> appropriate datatype.
> My assumption is that FLOAT is used by default when the '.' is
> specified. if you prefer to specify the precision and scale you could
> define the value as DECIMAL(10,4)
> e.g.
> DECLARE @.c_factor DECIMAL(10,4)
> SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
> correct typing
> PRINT @.c_factor
>
> DECLARE @.c_factor FLOAT
> SET @.c_factor = 12.0/44.0 -- mandatory decimal positions to ensure
> correct typing
> PRINT @.c_factor
>