Hello All, We are interested in limiting certain report stored procedures from running during certain times. This would cut down on server resources being used during critical sales times.I was planning on doing this programmatically buy creating an LTR table with the black out times (datetime) and creating a wrapper stored procedure that would call this table before the “real” stored procedure was executed. This seems like it would work for our custom reports.Has anybody Tried something like this before?
Thanks
Jason
That is a great problem to have! I am trying to get people to run reports *more* often.
I thought of three solutions to your problem that do not involve a "wrapper" proc, which in theory might work but would require you to go in and modify all your reports. For me that would be a deal breaker.
1. Look into the (NOLOCK) table hint; it reads tables without contending with live transactions (though you still may have performance issues; it will also give you "dirty reads" which may or may not be an issue).
2. Look to optimize the code in your SQL procedures themselves by using temp tables and fewer statements (of course this subject could take years to discuss).
3. Quick option: simply take the user groups' rights away at certain times of the day, then give them back. The table is TX_RPT_USERGROUP.
update tx_rpt_usergroupset run_right = 'N', view_right = 'N'where ug_id = 'my_group'and report_id = 'my_report_id'
Michael