##temp table already exists problem

[nigel]

Hello

I am using a temp table called ##temp in an SProc but often get the
message that the table already exists. Could this be because the SProc
is being run by more than 2 webpages at the same time?
Or is it because the sProc has an error and is not getting to the drop
table line?

I have tried adding a line to test if the object exists and to drop the
table before I create it. If I drop it will it affect another instance
of the sProc that is using the same table name?

If so is there a way around this?

Many thanks
Nigel

[Simon Hayes]

Global temp tables are visible to all connections, so if you have
multiple processes, then you can easily get errors like that. And if
you drop the table explicitly, one connection could drop the table
which was created by another connection.

Is there some specific reason why you can't use a local temp table? Or
perhaps you can use a permanent table, with @@SPID as part of the key?
If you can explain some more about what you're trying to do, then
someone may be able to suggest an alternative solution.

Simon

[nigel]

Thanks Simon.
So If I use # instead of ## the table is connection based rather than
global. Id did sort of know that but you have made it clear. I will
change to # instead of ##.

The situation is that I have a website that runs an SProc to get the
content of a banner. This runs for most pages on the site.
nigel