question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeThe #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:
> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
>> I have a stored procedure that creates a temporary table, populates it
>> with one record, and then returns that record to an ASP. I've read
>> that by prefixing the the tablename with a #, that only the connection
>> that created it can access it.
>> An IIS server will be making the connection and uses the same
>> connection string for all users. Even though the connection string is
>> the same, and the temporary table name is the same, will the temporary
>> table created only be available to the specific connection that created
>> it? I'm concerned that if multiple users make a request to that stored
>> procedure, they may get someone else's data.
>> thanks,
>> Joe
>>