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,
JoeIf you are populating the table with just one row, then use a table variable
instead.
AMB
"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
>|||If you are only populating with one row then chances are you can do this
without a temp table. But to answer your question no others can not see
this. But you might want to do as Alejandro says and use a table variable
instead.
Andrew J. Kelly SQL MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>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 even use a temporary table?
Declare variables, populate them, then return a recorset:
select @.variable1 as ..., @.variable2 as ... @.variable3 as ... etc.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>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
>|||Andrew,
I did not think about it, but yes, better to use an output parameter. What
about a connection taken from the pool, if the sp does not drop the temporar
y
table explicitly, and uses this code to create it:
if object_id('tempdb..#temptbl') is null
create table #temptbl ...
...
then next time the sp is executed using the same connection, a row inserted
by previous execution can be selected, correct?
AMB
"Andrew J. Kelly" wrote:
> If you are only populating with one row then chances are you can do this
> without a temp table. But to answer your question no others can not see
> this. But you might want to do as Alejandro says and use a table variable
> instead.
> --
> Andrew J. Kelly SQL MVP
>
> <joseph.fanelli@.vba.va.gov> wrote in message
> news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>
>|||If they are using connection pooling and follow the proper procedures a
sp_resetconnection is called that will clean up any such things and put the
connection settings back to the proper settings. Normally a temp table
created in a sp will go out of scope when the sp is completed as well. It
is only if they created the temp table outside of a sp that it will hang
around.
Andrew J. Kelly SQL MVP
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
> Andrew,
> I did not think about it, but yes, better to use an output parameter. What
> about a connection taken from the pool, if the sp does not drop the
> temporary
> table explicitly, and uses this code to create it:
> if object_id('tempdb..#temptbl') is null
> create table #temptbl ...
> ...
> then next time the sp is executed using the same connection, a row
> inserted
> by previous execution can be selected, correct?
>
> AMB
> "Andrew J. Kelly" wrote:
>|||Andrew,
You are right, the temporary table goes out of scope when the sp has
finished. I wonder what kind of cleaning is done by sp_resetconnection, I ca
n
not find anything about it in the BOL.
Thanks,
AMB
"Andrew J. Kelly" wrote:
> If they are using connection pooling and follow the proper procedures a
> sp_resetconnection is called that will clean up any such things and put th
e
> connection settings back to the proper settings. Normally a temp table
> created in a sp will go out of scope when the sp is completed as well. It
> is only if they created the temp table outside of a sp that it will hang
> around.
> --
> Andrew J. Kelly SQL MVP
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
>
>
