Profiler 2012 Error 208 before executing procedure - Exception raised, but procedure executes

1

Whilst running a SQL2012 Profiler trace on the Exception EventClass to verify some newly released code I stumbled on an odd issue I cannot explain.

The system I'm developing makes use of a lot of nested procedures which often use multiple temp. tables - I won't go in to the details, but it's the most efficient way to handle the data we're processing. Anyway, whilst running the trace I noticed a high volume of 208 Exceptions being
raised: Invalid object name '#name_of_temp_table'. Often multiple times for the same objects, but always a temporary table and always all of the temp. tables used in the procedure.

Whilst this may not seem strange in itself it is made strange by the fact that NONE of the procedures failed in the environment and all the data was processed as expected! To further confirm this behavior most of the procs have a TRY/CATCH that will log any error that occurs; no errors were captured or logged. Weird.

I then ran another trace with SP:StmtStarting and SP:StmtCompleted and SP:Starting and SP:Completed. I still got the exceptions, but they all occurred immediately before the SP:Starting event, but the temp. tables were those that we're used within the procedure. All the statements were then executed as expected and none of the statements failed - I paid special attention to those using the temp. tables and not a problem.

It's almost as though SQL is, for some reason, parsing the procedures before it's executing them. I thought this could be down to statement recompiles, but I've traced recompiles too and haven't seen a recompile at the same time I get the 208 Exception.

So whilst I'm a little happier that the system is working okay I cannot explain why we're getting these Exceptions and why they’re not causing an issue - and I want to be able to. I want to understand what SQL is doing so I can resolve - if possible/necessary.

Anyone got any thoughts??

2

I suspect it's a parsing issue. Is it doing an IF EXISTS/DROP TABLE at the top like you would do in an adhoc script?

3

Hello. That was one of my first thoughts, but nope. Well not all procedures - that's a bit down to the developer who wrote the code for the system. In general though no there are no EXISTS checks - I've suggested as it's contained within the procedure there is little point in checking for its existence or dropping on completion.

The issue occurs on those procedures without any explicit EXISTS/DROP or NOT EXISTS/CREATE as well as those with.

The fact is occurs before the SP:Starting suggests it has to be parsing, but it's really bugging me as to why this is happening...

Thanks for your reply...

4

It's a known issue called deferred name resolution: http://stackoverflow.com/questions/812522/how-to-find-what-caused-errors-reported-in-a-sql-server-profiler-trace

Unless MS decides to fix it, it's something that we have to live with in Profiler.

1 Like
5

Thanks for digging that article out. So it is down to parsing as suspected, but I'm much happier now I know why SQLServer is doing it. Is pretty poor though for profiler to capture what could be described as phantom errors - or at least non-fatal errors introduced as a result of using a temp. table. Anyway, many thanks for helping.

1 Like