Freigeben über

Enforcing timeouts on Report Builder queries

  • Artikel

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:

  1. Open and build the attached C# project in VS.
  2. Copy output assembly to the ...\ReportServer\bin folder.
  3. Add the config entries indicated in the NewConfigEntries.txt file (included in the project).
  4. 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)".
  5. Run a report against the report model that has a long-running query.
  6. 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.

SQLTimeoutDP.zip

Comments

  • Anonymous
    November 08, 2006
    I apologize if this is not the right place to ask a question, but I'm not familiar with blogs and I didn't see any other way to communicate to the RS Bloggers.   My question is:  Using Report Designer, what is a good way of formatting a word (e.g. bold, italics) within a phrase?    For example:  In the phrase, "SQL Server Reporting Services".   How can you bold the word "Server".   I have been doing it using multiple columns in a table to break up the phrase.   This leads to large gaps between the words using proportional fonts.   Thank you.
  • Anonymous
    October 10, 2007
    In SQL 2005 the semantic queries generated by Report Builder do not support sorting. This is not a problem
  • Anonymous
    October 10, 2007
    In SQL 2005 the semantic queries generated by Report Builder do not support sorting. This is not a problem
  • Anonymous
    November 12, 2007
    I tried your solution and it works beautifully. Is it however also possible to set the timeout not in the config file but programmatically? When I use 'GetDataSourceContents()' through the web service the string property 'Extension' shows I'm using the  'SQL-TIMEOUT' extension. But because it is a string property I cannot set config parameters through it. Is there another way?
  • Anonymous
    February 18, 2009
    As of SP3, it appears mapping semantic query engine to something other than Microsoft's built-in extensions is no longer supported. It will consistently bailout prior to ExecuteReader call. This worked prior to SP3.
  • Anonymous
    May 07, 2009
    I just uploaded an updated sample that fixes this issue. Thanks!
  • Anonymous
    June 08, 2010
    Looks like this won't work anymore, as of SQL Server 2008 R2. You can no longer access the constructor for Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper. Any ideas for a workaround?connect.microsoft.com/.../microsoft-reportingservices-dataextensions-sqlconnectionwrapper-now-internal
  • Anonymous
    December 21, 2010
    Will this work with Oracle as a datasource in SSRS 2005 Report Buidler 1.0 SP2
  • Anonymous
    March 06, 2014
    Bob Meyers MSFT: (and all of you struggling with the same question of SqlConnectionWrapper being Internal)Found a workaround here www.networksteve.com/.../Why_have_all_the_DataExtensions_clasess_become_internal_in_2008