Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a data source that is setup to store credentials on the report server and to use them when running reports. Given the following factors, I'm concerned about reports that use temporary tables in the SQL query text (not in a store procedure):

  1. Due to SQL Server's connection pooling, reports that use this data source will all share a single connection (assuming they are being run near simultaneously).
  2. Temporary tables are scoped on a per-connection basis.

For example, if User A runs the report with one set of parameters, the temp table will be populated with one set of data. If User B runs the same report at the same time with different parameters, the query will populate the temp table with a different set of data, thus possibly mangling User A's report.

Will the data in the temp table be shared across instances of a report and cause incorrect results?

share|improve this question
    
According to the answer given here, as long as sp_reset_connection is called (which is the default in ADO.NET), session temp tables should be dumped correctly between reports. –  Jon Seigel Mar 25 at 14:55
    
The report could be sharing a connection, but the temp table is scoped for the session. So you should never see cross-talk between reports. –  Mark Wilkinson Mar 25 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.