SQL Server Performance

SSRS Execution of Datasets within a report

Discussion in 'SQL Server 2005 Reporting Services' started by L0st_Pr0phet, Apr 23, 2009.

  1. L0st_Pr0phet Member

    I have a report that executes a sproc on a remote server that pulls back summary data and also creates two global temporary tables. Ideally I would like to use another two datasets to just do a select query against these tables. The problem is when the report runs, the execution of the datasets run in parellel and hence the two dataset based on the temporary (global) tables return a error that the objects do not exist (because the sp hasnt created them at time of execution).
    A messy way around it is using the waitfor function on the selects for a determined amount of minutes, but this in itself creates problems. Does anyone have a better way of doing this?
  2. satya Moderator

    Since the initial release of Reporting Services, there is a RDL setting for serializing dataset executions on the same data source connection (and therefore only using one data source connection for multiple datasets).
    To apply this setting, open the data source dialog in report designer, and select the "Use Single Transaction" checkbox. Once selected, datasets that use the same data source are no longer executed in parallel. They are also executed as a transaction, i.e. if any of the queries fails to execute, the entire transaction is rolled back.
    The order of the dataset execution sequence is determined by the top-down order of the dataset appearance in the RDL file, which also corresponds to the order shown in report designer.

Share This Page