Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Unanswered: Temporary tables in multi user environment


    I am developing reporting application (access project) which will be used in multi user environment.

    Here is what I have:

    1 SQLServer database for many users

    Each report will be based on:
    stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.

    what can you recommend - how to report in multi user environment?

    Many thanks

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    This problem arises when your stored procedure creates permanent tables in the database (even if it deletes the tables at conclusion).

    You need to use true SQL Server temporary tables, prefixed with a pound sign (#). These tables are invisible to all logins except the login that created them, so multiple users can run the procedure simultaneously without bumping into eachother.

    Alternatively, you can use table variables (Prefixed with the @ sign), which are often faster than temp tables but have limited indexing functionality.

    In either case, the temporary table or table variable will be automatically deleted or go out of scope when the procedure finishes.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Sep 2003
    1-#-temptables are not visible to the same login with different spid value. their scope is connection-bound.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts