May 16, 2012 at 5:58 am
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?
May 16, 2012 at 6:01 am
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
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 16, 2012 at 6:12 am
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?
May 16, 2012 at 6:22 am
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?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 16, 2012 at 6:36 am
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[/url], 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.
May 16, 2012 at 6:55 am
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.
Yes, I thought it was batch aborting. Doh! Good question, thanks.
May 16, 2012 at 6:59 am
Hugo Kornelis (5/16/2012)
According to this article[/url], 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.
Mystery solved. Above query returned 16384. When I added SET XACT_ABORT OFF to the script, I got 2 rows back.
Thank you Hugo for helping me figure this out!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 16, 2012 at 7:32 am
Hugo Kornelis (5/16/2012)
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[/url], 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.
Since the major objective of the QOTD is to teach, let me say. Thanks for taking the time to add more learning value to the QOD (for those that read the comments).
May 16, 2012 at 9:33 am
A very interesting one -- thanks, Ron!
May 16, 2012 at 9:53 am
baabhu (5/15/2012)
Nice Question. I was distracted by the factor of go will make it as one transaction.
Personally the gotcha wasn't the GO, it was figuring out if Management Studio would process the 3rd INSERT after erroring on the 2nd. For some reason I was thinking it would stop after the error instead of keep chugging along
May 16, 2012 at 10:59 am
great question - cheers
May 16, 2012 at 4:42 pm
Nice question, thanks!
May 16, 2012 at 9:44 pm
Great question and as always great discussion... 🙂
Thanks
May 17, 2012 at 10:35 am
I got this wrong thinking the entire batch would roll back. After playing around with it, now I know the transactions prior to the rollback will still be committed since a batch can have multiple transactions. However, since a batch is submitted as a unit, any syntax or resolution errors will cause the entire batch to rollback:
CREATE TABLE #temp
(id int not null,
data char(20) not null,
descr char(20)
CONSTRAINT PK_Temp PRIMARY KEY (id));
INSERT INTO #temp
VALUES (1, 'test1', 'testing1');
--Syntax error
INSERT INTO temp2
VALEUS (2, 'test2', 'testing2');
INSERT INTO #temp
VALUES (2, 'test3', 'testing3');
GO
SELECT * FROM #temp;
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'VALEUS'.
Msg 208, Level 16, State 0, Line 2
Invalid object name '#temp'.
May 17, 2012 at 1:01 pm
Great question...thanks.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply