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