Try Catch Throw

Question

Post reply

Try Catch Throw

Viewing 15 posts - 16 through 30 (of 30 total)

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

glen.putnam Old Hand Points: 345 More actions · Answer 1

I ran dbcc opentran after running this query.

I receive a no active open transactions message. If the transaction is still open why did I get this message?

webrunner SSC-Dedicated Points: 31882 More actions · Answer 2

Thanks, interesting question.

- 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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

The options provided were little confusing, I mean those were framed in such a manner that one fail to respond. However, I did it properly. Scored 2 marks!

Thanks.

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Revenant

SSC-Forever

Points: 42467

May 26, 2015 at 10:29 am

#1800637

Very nice question - thank you, Justin!

DataTherapist Mr or Mrs. 500 Points: 533 More actions · Answer 5

Ah, but I feel that all of your comments are missing the point of the question.

I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'

It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.

So just to check that my thinking is correct......

The TRY starts... and fails because it can't insert a row into a non-existant table

The TRY ends, and passes control to the CATCH.

Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?

Either way, is it correct to think that: If the @@TRANCOUNT was greater than 0, then it would rollback, and if it was zero, then there is nothing to rollback, so either way it passes by those 3 lines with no errors, yes/no?

In my thinking, in would have gotten to the 'THROW 50000.." line...

but you say that it doesn't, that it does "Msg 266, Level 16, State 2 etc. "

ARE BOTH messages generated by the statement-level recomplilation?

Message 1= the table doesn't exist

and

Message 2= since the table didn't exist, I bombed out, and couldn't read the whole proceedure, therefore I have a mismatch of BEGIN / END or BEGIN/COMMIT....

??

Love it.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Hany Helmy (5/26/2015)
OK, the author should have (like all other QotD) mention to select 2 answers, still it`s a very good question.
Thanx.

when I write questions I don't say how many answers to select; but Steve has always added it. Maybe there's been a change in policy?

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 7

DataTherapist (5/26/2015)
Ah, but I feel that all of your comments are missing the point of the question.
I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'
It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.
So just to check that my thinking is correct......
The TRY starts... and fails because it can't insert a row into a non-existant table
The TRY ends, and passes control to the CATCH.

Evidently you didn't read the explanation. Control is not passed to the catch block for this type of error when it occurs in code at the same level (in terms of nesting of execution blocks) as the try-catch. So the error in the select statement is hadled as if it wasn't in the try block and you get the standard error handling instead of the catch block.

Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?

Since execution never gets to that point (passes control to the CATCH block, it makes no sense to ask for the value of something at that point.

If XACT_ABORT is OFF (the default) the error in the select statement aborts the batch (no further statements in the batch are executed) but doesn't roll the transaction back, so when the batch exits trancount is still 1.

if XACT_ABORT is ON, the error in the select statement rolls back transactions to make trancount 0 before aborting the batch.

Tom

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Tom257 SSChampion Points: 11543 More actions · Answer 9

Am I out of date? I am the only one who missing something here?

Msg 102, Level 15, State 1, Procedure MyProc, Line 32

Incorrect syntax near 'THROW'.

Msg 2812, Level 16, State 62, Line 35

Could not find stored procedure 'dbo.MyProc'.

Whenever I have used TRY CATCH before, I've used RAISERROR.

Well yes. I am out of date. The new job has me demoted 🙁 -

SELECT @@VERSION

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Jun 17 2011 00:54:03

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Should have attempted the question on my home machine. 😀

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 10

Hi

The THROW is a new feature in SQL Server 2012 and thus it's not available in 2008 R2 yet.

Regards

Mike

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 11

akljfhnlaflkj

SSC Guru

Points: 76202

May 27, 2015 at 8:10 am

#1800930

Good question, thanks.

DataTherapist Mr or Mrs. 500 Points: 533 More actions · Answer 12

TomThomson (5/26/2015)
DataTherapist (5/26/2015)
Ah, but I feel that all of your comments are missing the point of the question.
I read it to mean 'pick one answer', because it didn't say 'pick two' or 'choose all that apply'
It succeeded in tricking me, because I wasn't aware that I would get 'compilation' errors in addition to the output of the proceedure.
So just to check that my thinking is correct......
The TRY starts... and fails because it can't insert a row into a non-existant table
The TRY ends, and passes control to the CATCH.
Evidently you didn't read the explanation. Control is not passed to the catch block for this type of error when it occurs in code at the same level (in terms of nesting of execution blocks) as the try-catch. So the error in the select statement is hadled as if it wasn't in the try block and you get the standard error handling instead of the catch block.
Question: Is the @@TRANCOUNT > 0 at this point? And if it is, is it because the BEGIN TRANSACTION inside the TRY incremented it - and the COMMIT didn't decrement it because it failed.. never got to the COMMIT inside the TRY?
Since execution never gets to that point (passes control to the CATCH block, it makes no sense to ask for the value of something at that point.
If XACT_ABORT is OFF (the default) the error in the select statement aborts the batch (no further statements in the batch are executed) but doesn't roll the transaction back, so when the batch exits trancount is still 1.
if XACT_ABORT is ON, the error in the select statement rolls back transactions to make trancount 0 before aborting the batch.

Thanks Tom for taking time to reply.

Peter A.R. Johnson

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 13

Bhushan Kulkarni

SSCrazy

Points: 2829

May 27, 2015 at 10:31 pm

#1801142

Good question

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 14

stephen.long.1

SSCrazy

Points: 2582

June 3, 2015 at 7:54 am

#1802622

Great question, Justin, thanks!

mannat SSC Rookie Points: 35 More actions · Answer 15

Why am i getting these two errors on executing?

Msg 102, Level 15, State 1, Procedure MyProc, Line 16

Incorrect syntax near 'THROW'.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'dbo.MyProc'.