2

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?

Improve this question
2
  • 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 '14 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 '14 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.