Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Transactions 1 Expand / Collapse
Author
Message
Posted Wednesday, May 16, 2012 3:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 7,700, Visits: 9,427
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
Post #1300868
Posted Wednesday, May 16, 2012 5:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:14 AM
Points: 1,258, Visits: 13,554
nice question!!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1300910
Posted Wednesday, May 16, 2012 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
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
Post #1300912
Posted Wednesday, May 16, 2012 5:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1300930
Posted Wednesday, May 16, 2012 5:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 17,633, Visits: 15,488
Nice question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1300931
Posted Wednesday, May 16, 2012 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 6,007, Visits: 8,269
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
Post #1300940
Posted Wednesday, May 16, 2012 6:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1300942
Posted Wednesday, May 16, 2012 6:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 6,007, Visits: 8,269
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
Post #1300947
Posted Wednesday, May 16, 2012 6:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:35 AM
Points: 1,825, Visits: 2,175
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
Post #1300951
Posted Wednesday, May 16, 2012 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 6,007, Visits: 8,269
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
Post #1300956
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse