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