This is a follow up to my article Missing
Temp Tables where I challenged readers to figure out what was wrong with
some VB code I provided - and offered to give up a few bucks to readers who met
the challenge! I was pleasantly surprised by the number of readers who took me
up the challenge. I'll announce the winners at the end of this article.
Most of the readers who responded correctly identified ONE of the problems
with the code - when I create the temp table I'm only selecting the au_id
column, so there is no way I can access the au_fname and au_lname fields in the
recordset loop further down without making some kind of change. Options
suggested where to include the fields in the temp table (good!) and to join back
to authors on au_id (sound, but not as good as adding the cols to the temp
table).
Only two readers took a crack at the problem I alluded to in the title - the
missing temp table. The suggested fixes were both provided in the form of MSDN
articles (Scoping
of Temporary Objects with EXEC and Error
Messaging Referencing #Temp Table with ADO-SQLOLEDB). Of the two, I think
the second one comes closest....but here is where it gets interesting.
When this first happened (the temp table missing), my first thought was I had
a typo, was just coding badly, etc. Thirty minutes later I was still getting the
same error. Ok, time to think instead of hack - why would the temp table be
missing? The only way the temp table should be gone is if I drop the table (I
definitely had not) or if I closed the connection (I had not!). Seeing no other
possibilities, I started Profiler and ran the code again - and watched the
connection close after the create table call!
Astounded doesn't come close to describing my reaction.
I went back to my code, checking again, realized that because it was a very
simple utility I had not bother to set the ADO connection to use a client side
cursor. Shouldn't matter though, right? I changed the cursor location, ran it
again - it worked! Strange, but ok. At least it works. Now the really strange
part - I tried the same code at home with a server side cursor - it worked fine!
Tried it the next day at work, worked without error there as well. Since then
I've seen the same thing happen to my friend Keith Lott - he would create a temp
table and then see an error on the next line when he tried to index the temp
table just created - changing the cursor location solved the problem.
I haven't found anything on MSDN that I feel explains this fully, especially
since I can't recreate the problem on demand. Connection pooling? Some leftover
setting? Who knows..... Anyway, I thought it would be interesting to see if
anyone else ran into the problem - and just to see if you were paying attention,
throw in the little glitch about not selecting enough fields into the temp
table. If you come up with a better answer than described here, let me know and
I'll make you famous in a follow up article.
Finally, the winners! As promised, I'm PayPal'ing $2 to the first person to
submit the correct answer, Mario Wolf Junior. The second place winners for this
contest are Brad Sprague, Benjamin Bolte, and Chris Barrett, each will be
receiving $1.
My thanks to all who participated! It was fun and I definitely got some
interesting email out of it! Look for another puzzle in early July - with a more
interesting prize.
