Enforcing timeouts on Report Builder queries
I've attached a sample C# project illustrating how to create a custom data processing extension (more info) that enforces a maximum timeout on any SQL queries submitted to it, including those generated from Report Builder.
To try it out:
- Open and build the attached C# project in VS.
- Copy output assembly to the ...\ReportServer\bin folder.
- Add the config entries indicated in the NewConfigEntries.txt file (included in the project).
- Use Report Manager to edit the properties for the data source your report model is bound to, and change the connection type to "Microsoft SQL Server (timeout)".
- Run a report against the report model that has a long-running query.
- Notice that the database query and report processing are cancelled after the timeout specified in the config file. This occurs regardless of the setting in the report (which RB always sets to 0=infinite).
Because mapping the SQL semantic query engine to something other than the built-in SQL data extension is technically not supported, the proverbial disclaimer applies: If you try this at home and it works, great; if something bad happens and you ask MS product support to fix it for you, they will tell you to go jump in a lake.
That said, hopefully some will find this helpful.
UPDATE: The new config entries identified in the attached file are based on a recent internal SP2 build. If you aren't running SP2 (not likely, since it hasn't been released yet), ignore the SemanticQuery entry and just copy your existing one, changing the name attribute to match the name of the new custom data extension.
UPDATE 5/8/2009: Updated attachment to fix issue in SQL 2005 SP3 and SQL 2008 (need to implement IDbConnectionWrapper). I updated the policy config entry to use the default install location for SQL 2008, and upgraded the project file to VS 2008. Thanks to hawdong for pointing out the issue.