Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gmcgahey
Starting Member
13 Posts |
 Posted - 2006-08-29 : 09:17:57
|
| HelloA hopefully simple question..I have a stored procedure, 'update_sales_results' which consolidates data and updates another database table 'sales_results' with the results.The table 'sales_results' is then used as the datasource for a set of reports built in RS.What is the easiest way of executing this stored procedure 'update_sales_results' BEFORE each report runs in order that the table 'sales_results' has the most recent data? I presume some custom code needs to be written for this purpose?thanks in advance |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-08-29 : 09:36:34
|
| well, do you have a proc to access the sales results?I would just add it as a statment in the results table proc.However, I would think that a job running every so often might be the ticket to update this table. An alternative would be to run this proc in the sales results proc and cache the report. Probably be alot faster for reporting, a quite a bit easier on your server if loads are high.________________________________________________"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks" |
 |
|
|
gmcgahey
Starting Member
13 Posts |
Posted - 2006-08-29 : 09:44:00
|
| thanks Vivaldi for your feedbackyes the 'sales_results' data is accessed using a select statement and this is the data source of the reports in RS. My question is how I can execute the stored procedure that populates this table in RS before the report is rendered to screen. The data is very time sensitive as the customer wishes to see the most up to date data possible on the report => running a scheduled job is not really an option. The stored proc itself runs very quickly. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-08-29 : 10:14:41
|
Well, since you aren't using a proc to get the data, the easiest option is out. I wonder if you add a 2nd data source and that proc as the source. Not sure how you could get it to run first though...I still think a job is the answer. or hook up this "update" code to the event that changes the underlying data... How are the customer accessing the report?quote:
Originally posted by gmcgahey
thanks Vivaldi for your feedbackyes the 'sales_results' data is accessed using a select statement and this is the data source of the reports in RS. My question is how I can execute the stored procedure that populates this table in RS before the report is rendered to screen. The data is very time sensitive as the customer wishes to see the most up to date data possible on the report => running a scheduled job is not really an option. The stored proc itself runs very quickly.
________________________________________________"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks" |
|
|
|
gmcgahey
Starting Member
13 Posts |
Posted - 2006-08-29 : 10:22:51
|
| Yes I will try adding another data source to execute the proc, but its probably not that simple!I suppose that I could create a job that runs the proc every 10 mins or so but that would probably cause uncertainty on the customer's side as they would ask why they are not seeing transactions just posted etc. Ideally, once the customer calls the report, the proc fires and the results table is updated with the latest data. The report is then generated based on the data in this table. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-08-29 : 10:37:22
|
Best/simplest wayGenerate a proc that returns the sales table data instead of pulling from the table directly.In that proc, before pulling the sales data, execute your "build sales data" proc. Its a clean, simple solution. quote:
Originally posted by gmcgahey
Yes I will try adding another data source to execute the proc, but its probably not that simple!I suppose that I could create a job that runs the proc every 10 mins or so but that would probably cause uncertainty on the customer's side as they would ask why they are not seeing transactions just posted etc. Ideally, once the customer calls the report, the proc fires and the results table is updated with the latest data. The report is then generated based on the data in this table.
________________________________________________"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks" |
|
|
|
gmcgahey
Starting Member
13 Posts |
Posted - 2006-08-29 : 10:39:43
|
| Many thanks Vivaldi I'll go that a go |
|
|
|
|
|
|
|
|
Execute a stored procedure before generate report