July 6, 2009 at 9:59 pm
Comments posted to this topic are about the item transaction test!
July 7, 2009 at 5:40 am
Good question!
(seeing as we regularly complain, thought I should show some support :))
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
July 7, 2009 at 6:02 am
Seconded.
(I lost my point - I was so focused on the details of the nested transactions that I failed to see that the code used a table variable instead of a temp or perm table - silly me!)
July 7, 2009 at 6:21 am
Hugo Kornelis (7/7/2009)
(I lost my point - I was so focused on the details of the nested transactions that I failed to see that the code used a table variable instead of a temp or perm table - silly me!)
Ditto!
Must wake up with more coffee before attempting QoTD!
Kev
July 7, 2009 at 6:34 am
Good question!
July 7, 2009 at 7:02 am
Very good catch.
July 7, 2009 at 7:46 am
Very good question.
How does this behavior stack up against ACID though ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 7, 2009 at 7:52 am
I thought this was great, and I learned something when this came through.
I don't know how this is related to ACID, but it's a nice little loophole for logging things. Since it's not a "Real" table, but a variable, I assume this doesn't impact integrity.
July 7, 2009 at 8:17 am
Great question. I lucked out by picking the right answer for the wrong reason: I mistakenly thought committing transaction T2 committed T1 as well, not that table variables ignore transaction statements.
Shows me how much more I have to learn.:w00t:
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 7, 2009 at 10:58 am
Hee, hee. I, too got this right for the wrong reason. Like Webrunner, I thought the commitment of T2 committed T1. Oops.
Good question.
July 7, 2009 at 12:23 pm
I missed too! I knew the correct answer, if I had noticed it was using table variables. Missed that, choose the wrong answer.
July 7, 2009 at 12:30 pm
Can any one please help me...
when i execute the following query,
BEGIN TRANSACTION T1
INSERT INTO table_1 VALUES ('ABA','xxx',130)
COMMIT TRANSACTION T1
BEGIN TRANSACTION T2
INSERT INTO table_1 VALUES ('ABB','ccc',510)
ROLLBACK TRANSACTION T2
select * from table_1
i'm getting this warning..
"(1 row(s) affected)
(1 row(s) affected)
Msg 6401, Level 16, State 1, Line 6
Cannot roll back T2. No transaction or savepoint of that name was found."
is anything wrong with query?
Ramu
No Dream Is Too Big....!
July 7, 2009 at 1:20 pm
ramu.valleti (7/7/2009)
Can any one please help me...when i execute the following query,
BEGIN TRANSACTION T1
INSERT INTO table_1 VALUES ('ABA','xxx',130)
COMMIT TRANSACTION T1
BEGIN TRANSACTION T2
INSERT INTO table_1 VALUES ('ABB','ccc',510)
ROLLBACK TRANSACTION T2
select * from table_1
i'm getting this warning..
"(1 row(s) affected)
(1 row(s) affected)
Msg 6401, Level 16, State 1, Line 6
Cannot roll back T2. No transaction or savepoint of that name was found."
is anything wrong with query?
Hi Ramu,
I copied and pasted that SQL in a new query window, added the CREATE TABLE for table_1 before it and a SELECT * FROM table_1 after it, and I got no errors. Only the first row was returned, as expected (since the second insert was rolled back).
Are you sure this is the exact code you tried?
July 7, 2009 at 4:04 pm
Seems a bit silly to have a transaction that doesn't apply to table variables - I think I'll have to go and read MSDN to find out a bit more!
Great question by the way - just goes to show that I don't know everything (even though my manager seems to think so).
Off to go fix up world peace now!
July 8, 2009 at 4:03 am
This was a nice thing to learn! Thanks to all for the Boquets in this one and Brickbats in others! 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply