Transactions

Question

Transactions

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Straight forward - thanks for the Question Ron.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

By including this in my justification of the correct answer:

I owe the idea for this question from a blog post by:SQL RNNR - Cirquede SQLeil

https://www.google.com/reader/view/?tab=my#stream/feed%2Fhttp%3A%2F%2Fjasonbrimhall.info%2F%3Ffeed%3Drss2%5B/quote%5D

I had hoped to bring to the attention of those who answered the question.

1. The effects of including TRANSACTIONS in their T-SQL statements on both data and DDL.

and

2. That there are many additional sources of information about SQL such as Blogs, and I wondered how many visitors to SSC actually click on "Blogs" in the left most frame. And if they do not, get them to do so, even if only occassionaly.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

CirquedeSQLeil (11/24/2010)
Straight forward - thanks for the Question Ron.

See my other post, it is you who gave me the idea for the question, and I thank you for that, and all the other posters who said thank you to me, should and are also saying thank you to you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

bitbucket-25253 (11/24/2010)

By including this in my justification of the correct answer:

I owe the idea for this question from a blog post by:SQL RNNR - Cirquede SQLeil
https://www.google.com/reader/view/?tab=my#stream/feed%2Fhttp%3A%2F%2Fjasonbrimhall.info%2F%3Ffeed%3Drss2%5B/quote%5D
I had hoped to bring to the attention of those who answered the question.
1. The effects of including TRANSACTIONS in their T-SQL statements on both data and DDL.
and
2. That there are many additional sources of information about SQL such as Blogs, and I wondered how many visitors to SSC actually click on "Blogs" in the left most frame. And if they do not, get them to do so, even if only occassionaly.

D'oh, shame on me. I skipped right past the references having gotten it correct. Thanks for the reference Ron.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

I lost it. Nice question. Leared/reminded something today.

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 6

Dhruvesh Shah

SSCrazy

Points: 2303

November 25, 2010 at 8:48 pm

#1255681

Easy point.

randombod Newbie Points: 7 More actions · Answer 7

DhruvShah (11/25/2010)
Easy point.

I dont know why I was so narked (Australian Slang; annoyed) with your response, perhaps it is because you have 2 accounts and looked up the answer first and then responded, or perhaps (more likely) your responses are so limited in actual information they are useless.

No doubt this will get flagged or reported but DhruvShah - please(!) - please respond with a bit more than what you did and have since your membership...

narked Newbie Points: 5 More actions · Answer 8

randombod (11/26/2010)
DhruvShah (11/25/2010)
Easy point.
I dont know why I was so narked (Australian Slang; annoyed) with your response, perhaps it is because you have 2 accounts and looked up the answer first and then responded, or perhaps (more likely) your responses are so limited in actual information they are useless.
No doubt this will get flagged or reported but DhruvShah - please(!) - please respond with a bit more than what you did and have since your membership...

Hey thats my namesake!

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 9

Hi,

I tried by typing everything in. I can get nothing but the error message "[font="Courier New"]invalid object name 'dbo.items'[/font]". 🙁

Best regards,
Dietmar Weickert.

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Nakul Vachhrajani (11/24/2010)
Hello!
This was very similar to one of my own QotDs a few months back.
However, what I take from this one is that the DDL is not committed automatically because it is wrapped inside another transaction, which when rolled back, rolls back everything till the outermost BEGIN TRAN.
I also did the test by using IMPLICIT_TRANSACTIONS ON/OFF, and the result was the same.

I don't understand how it could produce the same result with IMPLICIT_TRANSACTIONS on, because it should not have been in autocommit mode in that case; the insert in the first batch should not have been committed, and the implicit transaction that it started should have been the outermost transaction rolled back by the rollback statement in the second batch (normal batch termination does not commit the current transaction). Are you sure that you got the same result with autocommit off as with it on?

Tom

UMG Developer SSChampion Points: 13482 More actions · Answer 11

Thanks for the question, and the discussion about ; after GO. I knew you couldn't use a ; after GO, but I never realized what can happen because of it.