SSRS ReportExecutionServce.LoadReport gets stuck

Question

Consider the following example code:

using System.Data.SqlClient;

namespace ReportLoadTest
{
  class Program
  {
    static void Main(string[] args)
    {
      using (var con = new SqlConnection("...your connection string here..."))
      {
        con.Open();
        var trans = con.BeginTransaction();
        var cmd = con.CreateCommand();
        cmd.Transaction = trans;
        cmd.CommandText = @"insert SomeTable(...columns...) values (...); select scope_identity()";
        var rows = cmd.ExecuteScalar();

        var rs = new SSRS.ReportExecutionService();
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
        rs.Url = "http://localhost/ReportServer/ReportExecution2005.asmx";

        var ei = rs.LoadReport("/Folder/Folder/Some report", null);
      }
    }
  }
}

Under what conditions would this program "get stuck" at the call to ReportExecutionService.LoadReport?

By stuck, I mean 0 CPU, 0 I/O - no progress being made at all by the calling program, Reporting Services or SQL Server.

Answer 1

This program will get stuck if the report that's being loaded contains a dataset that's used to populate the available values for a parameter and that dataset is based on a query that reads rows from SomeTable.

LoadReport will eventually time out and there will be zero helpful information left lying around to help you figure out what happened.

Possible solutions:

• Change the report to do "dirty reads" on SomeTable
• Change the database to snapshot isolation mode to avoid the lock on the table.

I ran into this as an actual production issue with a system that runs reports on a schedule, and the report being run was the "scheduled reports history" report.

The subtlety is that LoadReport runs queries - in retrospect, it's obvious that it must run queries since the available values for parameters are contained in the ExecutionInfo that's returned by LoadReport.