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,
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...
>
>

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,
Joe
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
>
|||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...b;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> 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:

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,
Joe
Connections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegr oups.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
>
sql

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/defaul...kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> 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}. Th
is
> 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:
>

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,
JoeConnections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.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
>