June 5, 2008 at 2:08 pm
I came sccross a problem where Try/Catch block did not catch an error. Here is a part of my code where I try to bulk insert a file that have records longer that declared length of a table to insert to. It should trigger a message as bulk load data convertion error.
Here is my code:
declare
@stmt varchar(max),
@ char(1)
set @ = char(39)
create table #temp1
(data varchar(300))
set @stmt = 'bulk insert #temp1 from ' + @ + 'd:\temp\firms\firmmanifest.xml' + @
begin try
execute (@stmt)
end try
begin catch
select error_number()
select error_message()
end catch
However if I replace it with old-fashined @@error like the following script, it works fine.
declare
@stmt varchar(max),
@ char(1)
set @ = char(39)
set @stmt = 'bulk insert #temp1 from ' + @ + 'd:\temp\firms\firmmanifest.xml' + @
execute (@stmt)
select @@error
The output is:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (data).
-----------
4863
I checked B.O.L. It states that Try/Catch block should catch errors with severity level greater than 10. But my level is 16 !
Am I coding something wrong in my Try/Catch block? Or maybe it's better to still to use old @@error ?
Thanks
June 5, 2008 at 3:41 pm
I've found, in my experience with Try/Catch on dynamic SQL, that you either need to have the Try/Catch in the dynamic SQL statement, or you need to modify your code as follows:
declare
@stmt varchar(max),
@ char(1),
@err int
set @ = char(39)
create table #temp1
(data varchar(300))
set @stmt = 'bulk insert #temp1 from ' + @ + 'd:\temp\firms\firmmanifest.xml' + @
begin try
execute @err = (@stmt)
if @err <> 0 raiserror('Your error message here', 16, 1)
end try
begin catch
select error_number()
select error_message()
end catch
You can either use the raiserror to create a custom error message, or use it with @err to get the error message from the dynamic SQL.
I don't know why it works that way, and maybe I'm doing something wrong with it, but it does seem to work that way for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 4:10 pm
I tried to reproduce your code, but it complains about (@stmt), apparently you did not test this code prior to posting.
But my point anyway is that Catch block cannot actually catch all the errors (unless I am doing something fundamentally wrong). If I am correct, using Try/Catch can be even misleading, like in my case it did not report any errors but the file was not loaded either.
June 5, 2008 at 4:17 pm
I'll have to do some more research, but I seem to remember reading somewhere that TRY/CATCH in T-SQL doesn't catch all errors. I'll try and find where I saw that and post back if I find it.
😎
June 5, 2008 at 8:52 pm
Not sure about the rest, but this is surely wrong:
Mark Shvarts (6/5/2008)
begin catchselect error_number()
select error_message()
end catch
I believe that this should be:
begin catch
select error_number(),
error_message()
end catch
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 6, 2008 at 7:51 am
It does not matter. I changed it to one select statement but result is the same, Catch does not catch the error.
June 6, 2008 at 8:04 am
TRY..CATCH has pretty complicated rules and I have to admit that I still didn't learn it properly (on the other hand, we have SQL2005 only a few weeks and I didn't need it yet). I decided to postpone it until I really need to use it.
From BOL:
A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements.
TRY…CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
- Compile errors, such as syntax errors, that prevent a batch from running.
- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
These errors are returned to the level that ran the batch, stored procedure, or trigger.
June 6, 2008 at 8:28 am
Have you tried what GSquared suggested? I beleive that this (putting TRY..CATCH in the inner execution) is on the right track.
What I have ended up doing in my toughest cases with TRY..CATCH handling is to put TRY..CATCH in both the inner and outer execution contexts. Then on the Inner, I preserve the ERROR_MESSAGE and RAISERROR setting the severity to 16. The outer TRY..CATCH cathes all of these as well as almost all of the stuff the the inner one cannot catch.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 6, 2008 at 10:19 am
Yes I tried GSquared suggestion. Post #512562 .
June 9, 2008 at 11:55 am
Mark Shvarts (6/5/2008)
I tried to reproduce your code, but it complains about (@stmt), apparently you did not test this code prior to posting.But my point anyway is that Catch block cannot actually catch all the errors (unless I am doing something fundamentally wrong). If I am correct, using Try/Catch can be even misleading, like in my case it did not report any errors but the file was not loaded either.
Didn't test what? It's a copy-and-paste of your proc with one minor change. I've tested that change, by using it hundreds of times in the last 7 years.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 12:17 pm
Heh.
Yes, GSquared, we expect that the free work donated by the unpaid experts at this site reflect a higher standard than that. :rolleyes:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 4:11 pm
Maybe this has changed in SQL9, but I don't think you can use EXEC @retval = (@sqlstring). If you use sp_executesql (or any other sp) you can catch a return value in this way, but not with EXEC(@sqlstring). You could still propagate the final error status from the dynamic sql batch up to the calling context, to be raised and caught there, as suggested, but you would have to use sp_executesql or @@error in the TRY block.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 12, 2010 at 2:02 am
Use exec @err = sp_executesql @stmt to fix the compliation error
July 12, 2010 at 2:04 am
Renuka Sajjan-166311 (12/7/2010)
Use exec @err = sp_executesql @stmt to fix compilation error
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply