June 7, 2012 at 1:05 pm
Hello,
I have many SQL reports in one project. Each report has its stored procedure on the database. I have a procdure load_data that loads data into a global table from a text file. The reports' procedures check if the global temp table exist and calls procedure load_data. However, I figured that each reports is calling load_data each time I call them. Does each report connects using a different database session ?
Is there anyway to make it use the same session ?
Thank you
June 7, 2012 at 1:27 pm
Sounds to me like global temp tables is not the best approach here. How do you handle multiple users getting different reports at the same time?
To directly answer your question, no you can't decide which connection each report is going to use.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 6:36 pm
hello,
The procedure that reads the data from the text file creates a unique table name by attaching the username to the table name. This makes the table unique. The data is being read from a log file, and this gets updated live by other systems.
Thank you
June 7, 2012 at 11:29 pm
Hi Beginner,
I don't like the idea of updating a 'temporary table' by your procedure, even though it gets an unique name, I will suggest implementing an SSIS package taking care of the task of updating a permanent table from the log file, this package can be scheduled to run every hour, or as often as required, then your reports will retrieve the data from the permanent table maintained by the SSIS package.
Cheers,
Hope this helps,
Rock from VbCity
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply