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??