May 12, 2008 at 11:57 am
I have several files that I import using generic procedures that create temp tables with the data based on the schema in the file. I then query the temp table with the column names that are supposed to be in the file. The problem is the files should be consistent, but sometimes aren't, and I'm having problems catching the error. As an example:
create table #foo (a int)
print 'begin'
begin try
select b from #foo
--insert into #foo values ('asdf')
end try
begin catch
print 'error!'
end catch
drop table #foo
This throws an invalid column error that doesn't get caught by the catch block. It doesn't even drop the table, so execution is stopping when the error is found. If you uncomment the insert and comment the select, the data type conversion failure gets caught fine.
I was thinking it was an issue with parse time versus compile time versus run time or something that occaisionally shows up in interpreted languages. However, the above does print "begin" so it's not erroring out as soon as the table is created and it realizes the rest is going to error.
Is there some way to make the error handling work? Yes, I can query the system tables and see what the schema actually is, but that defeats half the point of having generic procedures for importing and is a pain for large files with many fields. This seems like something the try/catch error handling should cover. Any ideas?
Thanks.
Andrew
May 12, 2008 at 12:51 pm
You might want to try an SSIS package.
SSIS can check the data as it is being loaded. If it finds a value that does not pass the appropriate checks it can shift the record out to a error table for you review later and only load the records that pass all the checks.
Dave Novak
May 12, 2008 at 12:54 pm
This seems like a compile error, which should mean that the [font="courier"]Print 'Begin'[/font] never executes either. Are you doing this in Dynamic SQL?
[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]
May 12, 2008 at 1:04 pm
I was just looking at some of the info on 'TRY .... CATCH' blocks.
One of the item it states is if the severity of the error is 10 or less, then the 'TRY' block will not catch it and pass the control to the 'CATCH' block. This may have something to do with you issue.
Dave Novak
May 12, 2008 at 2:42 pm
If I use SSIS, then I have to maintain a separate SSIS package for each file format I import. For each format, there's going to be some custom processing done in a stored procedure, so I'd rather, for maintainability, keep everything in one place. If the invalid column exception would be caught properly in the T-SQL, I'm perfectly happy with that error handling.
I first thought it was some kind of compile-time error, too, but the print 'begin' statement does indeed print begin. If you run that in a query window a second time right after the first, the exact same error does show up as a compile-time error, since it stopped execution before it got to the drop table statement. The temp table exists at compile time in that case and it never starts (and doesn't print begin).
The temp table itself is being created and populated with dynamic SQL. In some cases, it's dynamic SQL in a T-SQL stored procedure (eg delimited text), and in other cases it's done via a CLR function (eg Excel). In both cases, the import process is completely ignorant of any expected file format and just looks at the file itself for a schema and creates a table with that schema.
Running the above snippet throws the invalid column error and SSMS shows it as error level 16, so it's definitely above the threshold that's supposed to be caught. The invalid cast in the insert statement is also level 16 and that gets caught just fine.
I appreciate the ideas. There are work-arounds I can use, and I'd be more inclined to bite the bullet and use them if I can find a reason for why the behavior is as it is. I can't think of any reason SQL Server would skip the catch block for this particular error. I want to say that I've seen it skip other errors, too, but this is the only one I ran across recently and could remember.
Is it a compile-time error, and each line in the batch is compiled just before it is executed, and compile time errors can't be caught? That's the closest rationale I can come up with and was looking if someone could back that up or refute it.
Thanks a lot for your help.
Andrew
May 12, 2008 at 3:06 pm
Andrew -- the Try...Catch construct does not catch statement-level recompilation errors, such as errors in deferred name resolution.
Take a look at this BOL link for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm
Look for the section "Errors Unaffected by a TRY...CATCH Construct. Also look at the link in that section for hints on how to work around it using sp_executesql or stored procedures.
GL!
May 12, 2008 at 3:19 pm
Thank you, that's the confirmation I was looking for. I feel particularly dumb now that it was in the obvious BOL page all this time. Apparently it's a scope-abortion error, as described here: http://www.sommarskog.se/error-handling-I.html#scope-abortion
Thanks again, everyone, for your help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply