December 17, 2013 at 9:25 am
Hi There,
Please find the following query,
begin try
select top 10* from Sampletable(nolock)
end try
begin catch
print('table not available')
end catch
Actually above table is not available in the database. So I expect my query would result "table not available"
but it results
Msg 208, Level 16, State 1, Line 3
Invalid object name 'vw_cdr'.
Please explain why try catch not working?
December 17, 2013 at 9:28 am
The catch is a run-time error handler, but the error you are getting is a parse time error that happens before the query is run.
December 17, 2013 at 9:29 am
It's a query parsing error. SQL hasn't got as far as running it.
Try this:
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
RAISERROR('Divide-by-zero error detected.', 16, 1)
END CATCH
December 18, 2013 at 3:05 am
Michael Valentine Jones (12/17/2013)
The catch is a run-time error handler, but the error you are getting is a parse time error that happens before the query is run.
I agree with you ...
let me clarify one more thing ...
If I parse the above query using ctrl+f5 it wont give any error. Then how we say it is a parse time error?
December 18, 2013 at 3:15 am
vignesh.ms (12/18/2013)
If I parse the above query using ctrl+f5 it wont give any error. Then how we say it is a parse time error?
Because it occurs before execution starts. The Parse option in SSMS just checks syntax, not whether or not objects exist. That's done in the binding phase which occurs after parsing but before optimisation, well before runtime, which is when TRY...CATCH executes.
p.s. You do know the downsides and side effects of nolock, since it appears to be a habitual use?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2013 at 6:19 am
GilaMonster (12/18/2013)
vignesh.ms (12/18/2013)
If I parse the above query using ctrl+f5 it wont give any error. Then how we say it is a parse time error?Because it occurs before execution starts. The Parse option in SSMS just checks syntax, not whether or not objects exist. That's done in the binding phase which occurs after parsing but before optimisation, well before runtime, which is when TRY...CATCH executes.
p.s. You do know the downsides and side effects of nolock, since it appears to be a habitual use?
Im a newbie .. I dont know the downsides and side effects of nolock..
Could you assist me regards this ?? any articles ?
December 18, 2013 at 6:23 am
vignesh.ms (12/18/2013)
Im a newbie .. I dont know the downsides and side effects of nolock..
So why do you use it if you don't know what it does?
Could you assist me regards this ?? any articles ?
Plenty. Try google.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2013 at 6:37 am
All I know is ,
It will give us the dirty data ...
(ie)if update is going on, then it would not care about current values.. it will result whatever there on the db while selecting
December 18, 2013 at 6:42 am
That's the less problematic part of it.
From books Online:
Missing and Double Reads Caused by Row Updates
Missing one or more rows that were not the target of update
When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits.
You can read rows multiple times for the same reason.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply