"Chamark" <u21870@uwe> wrote in message news:63a0a1741525b@uwe...
> Using MS-SQL2000 User Level - Beginner
>
> My application needs to take variables from a Web form pass them to a
> table
> for my multiple Views to use. Currently I am using an already created
> table
> and doing a delete each time the application is run. I know this method
> can
> cause problems in a multi-user environment, especially when I am not
> locking
> the table.
>
> I understand that temporary tables can be created either with a # or a
> Declare @ (for Data Type) and these tables are automatically dropped at
> the
> end of the connection. That I can store variables from the user in these
> temp
> tables that can be used by my Views. Is the temporary table created each
> time
> a user runs the application and if so, does each user have their own temp
> table? Is Data Type a better way to go than  #temp? Doesn't the table get
> stored in TempDB and will I run into the name of the table already being
> used
> condition?
>
> How / where do I create these type tables since I can't use Declare's in
> Views? Stored Procedure or UDF? And can I use an EXEC in a View. Thanks in
> advance for your assistance
>
> --
> Message posted via http://www.sqlmonster.com

>> My application needs to take variables from a Web form pass them to a
>> table for my multiple Views to use. Currently I am using an already
>> created table and doing a delete each time the application is run. I
>> know this method can cause problems in a multi-user environment,
>> especially when I am not locking the table.
>
>It's not entirely clear to me: is this data stored during the session
>that the user is active and accessed repeatedly? Or are they just data
>that the user enters once and then is used as input to queries?
>
>In the former case, add a session_id key to the table sa Dan said, to keep
>users apart. I think think this is the best solution for you.
>
>In the latter case, I don't see any need for any table at all.
>
>> I understand that temporary tables can be created either with a # or a
>> Declare @ (for Data Type) and these tables are automatically dropped at
>[quoted text clipped - 4 lines]
>> Doesn't the table get stored in TempDB and will I run into the name of
>> the table already being used condition?
>
>Table variables, those with @ in the beginning lasts only for the
>duration of the batch, so if the data is to be stored to the end
>of the session, table variables are not an alternative.
>
>Temp tables (names starting with #) exists until the connection is
>closed if they are created in the top-level scope of the connection. If they
>are created in a stored procedure, they go away when then procedure exits.
>Temp tables are local to the connection, and cannot be access by other
>users.
>
>I don't think temp table is a good solution for your purpose. Most
>applications today, and particular web applications, do not stay connected.
>Rather the norm is connect, issue a query, disconnect. (Behind the
>scenes the client API maintains a connection pool to reduce the amount
>of physical connection/disconnection.) To use a temp table you would
>have maintain a connection that stays up all the time, and this us
>undesireable in a web application.
>