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.