SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions 1


Transactions 1

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26076 Visits: 12500
Good question.

I managed to get it wrong (thinking the error terminated the batch - as would an "insert value in identity column with identity insert ON" error, for example - so that the answer would be 1); serves me right for answering before breaking my fast, I guess.

There appears to be a bug in the parser which is shown up by this question (not insisting on a comma which thepublished BNF shows as essential). I guess it's a pretty harmless bug, though.

And why oh why does the explanation refer to sql 2000 documentation instead of to documentation for one of the currently supported releases?

Tom

rfr.ferrari
rfr.ferrari
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2169 Visits: 13639
nice question!!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15871 Visits: 25280
Hugo Kornelis (5/16/2012)
Nice question. I knew about implicit transaction, but I had to do some digging because I couldn't remember if primary key violation errors are batch-aborting or not.

(One possible -slight!- improvement to the question would have been to explicitly mention that the three insert statements are executed as a single batch; the current wording can be interpreted as them being executed one by one).


Prior to submitting the question, I tested as a batch, then executing one insert statement at a time. Made no difference to what happens.

have not tested if the code as posted would actually work or complain about the column reference in the column constraint. If it does work, I would classify it as a parser bug, since the description in BOL implies that the seperating column between columns and table constraints is mandatory.


Retested the CREATE TABLE statement not more than 2 minutes ago
CREATE TABLE QODT1(Col1 int NOT NULL,col2 CHAR(1) NOT NULL,col3 VARCHAR(20)
CONSTRAINT PK_QODT1 PRIMARY KEY
(col1))


It worked when I initially created the question, and it works today.

L' Eomot Inversé
In answer as to why I cited as a reference, the statement for SQL 2000, sort of a tongue in cheek action on my part, to show people how long the IMPLICIT TRANSACTIONS feature/rule has been in effect.

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

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3841 Visits: 2256
Thanks for the question.

Got it wrong, and I even ran a test. Code below:


CREATE TABLE #Temp(Col1 INT NOT NULL
CONSTRAINT PK_QOTD_1 PRIMARY KEY (Col1))

INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (2)
GO

SELECT * FROM #Temp



(not exactly the same code as the question, but functionally equivalent) Only got one row back from SELECT, as I had anticipated.

So, what have I got wrong here?

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67093 Visits: 18570
Nice question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 12426
Thomas Abraham (5/16/2012)
Thanks for the question.

Got it wrong, and I even ran a test. Code below:


CREATE TABLE #Temp(Col1 INT NOT NULL
CONSTRAINT PK_QOTD_1 PRIMARY KEY (Col1))

INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (2)
GO

SELECT * FROM #Temp



(not exactly the same code as the question, but functionally equivalent) Only got one row back from SELECT, as I had anticipated.

So, what have I got wrong here?

No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3841 Visits: 2256
Hugo Kornelis (5/16/2012)No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?


SQL Server 2008

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 12426
Thomas Abraham (5/16/2012)
Hugo Kornelis (5/16/2012)No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?


SQL Server 2008


The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).
Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3841 Visits: 2256
Hugo Kornelis (5/16/2012)The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).
Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?


I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 12426
Thomas Abraham (5/16/2012)
Hugo Kornelis (5/16/2012)The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).
Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?


I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?


According to this article, you can check the current setting of SET XACT_ABORT by running SELECT 16384 & @@OPTIONS; - if the result is 16384, it is on; if it's 0, the option is off.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search