How can you run a report from the ReportServer database without building subscriptions?

Question

I'd like to build a back end system that allows me to run each report every night and then query the execution log to see if anything failed. I know you can build out subscriptions for these reports and define parameters etc but is there a way to execute each report from the ReportServer database using TSQL without building out each subscription?

Answer 1

I understand that your overall goal is that you want to automate this and not have to write a subscription for every report. You say you want to do it in T-SQL, but is that required to meet your overall goal?

If you can accept, say .Net, then you can use the System.Data.SqlClient.SqlConnection and related classes to query your report server catalog and fetch a listing of all your reports.

Then you can use System.Net.WebClient or similar tool to attempt to download a pdf of your report. From there you can either read your execution log, or catch the error in the .Net Code.

EDIT

Well, since you accepted the answer, and it seems you may go this route, I'll mention that if you're not familiar with .net, it may be a long path for you. Here's a few things to get you started.

Below is a c# function utilizing .Net that will query the report catalog. If safeImmediate is set to true, it will only capture reports that can be run immediately, as in there are no parameters or the defaults cover the parameters.

IEnumerable<string> GetReportPaths(
    string conStr,
    bool safeImmediate // as in, you can exexute the report right away without paramters
) {

    using (var con = new SqlConnection(conStr))
    using (var cmd = new SqlCommand()) {

        cmd.Connection = con;
        cmd.CommandText = @"select path from catalog where type=2";
        con.Open();

        if (safeImmediate) 
            cmd.CommandText = @"

                select      path
                from        catalog 
                cross apply (select 
                                params = convert(xml, Parameter).value('count(Parameters/Parameter)', 'int'),
                                defaults = convert(xml, Parameter).value('count(Parameters/Parameter/DefaultValues/Value)', 'int')
                            ) counts
                where       type = 2  
                and         params = defaults
                and         path not like '%subreport%' -- this is not standard.  Just works for my conventions

            ";

        using (var rdr = cmd.ExecuteReader())
        while (rdr.Read()) 
            yield return rdr["path"].ToString();

    }

}

The next function will download a report given proper paths passed to it:

byte[] DownloadReport (
    WebClient wc, 
    string coreUrl, 
    string fullReportPath, 
    string parameters = "" // you won't use this but may come in handy for other uses
) {
    var pathToViewer = "ReportServer/Pages/ReportViewer.aspx"; // for typical ssrs installs
    var renderOptions = "&rs:Format=pdf&rs:Command=Render"; // return as pdf
    var url = $@"{coreUrl}/{pathToViewer}?{fullReportPath}{parameters}{renderOptions}";
    url = Uri.EscapeUriString(url); // url's don't like certain characters, fix it
    return wc.DownloadData(url); 
}

And this utilizes the functions above to find what's succeeding and whats not:

    var sqlCon = "Server=yourReportServer; Database=ReportServer; Integrated Security=yes"; // or whatever
    var ssrsSite = "http://www.yourSite.org";

    using (var wc = new WebClient()) {

        wc.UseDefaultCredentials = true; // or whatever

        int loops = 3; // get rid of this when you're ready for prime-time

        foreach(var path in GetReportPaths(sqlCon, true)) {

            try {
                DownloadReport(wc, ssrsSite, path);
                Debug.WriteLine($"Success with: {path}");
            }
            catch(Exception ex) { // you might want to get more specific
                Debug.WriteLine($"Failed with: {path}"); 
            }

            if (loops-- == 0)
                break;

        }

    }

Lots to learn, but it can be very beneficial. Good luck.