Can I execute an SSRS report from a windows batch file?

Question

We are using SQLServer 2008R2 and would like to run and SSRS report from a windows batch file. We're loading data warehouse tables using a third party scheduler program and at the end our dataload we want to run a data quality report using SSRS. I can create report subscribtions to email the report but how do I communicate to the report server that the job is done and it's time to run the report? OUr scheduler application can run batch files so if I could pass this to the report server through a batch file that would be great...

score 0 · Answer 1 · answered Mar 03 '11 at 21:40

Sure, but it's a little clunky. When you create a subscription to a report, Reporting Services will create a job in SQL Server Agent. Unfortunately, the job will have a thoroughly unhelpful name - a random GUID. Use the below query in your report server catalog database to list the job names that go with each report schedule (the ScheduleID column). Once you've figured out which job invokes the subscription, you can use sqlcmd in a batch file to either run the SQL Server Agent job (msdb..sp_start_job), or look at the step(s) in the job and execute them directly. I'd opt for executing the job, personally, so that there will still be execution history.

SELECT
    sub.Description,
    CASE
        WHEN sub.ExtensionSettings LIKE '%<Name>TO</Name><Field>%' THEN '(Data Driven)'
        ELSE SUBSTRING(CAST(sub.ExtensionSettings AS varchar(8000)), 56, ISNULL(NULLIF(CHARINDEX('</ParameterValue>', CAST(sub.ExtensionSettings AS varchar(8000))) - 8 - 55 - 1, -63), 1))
    END AS SendTo,
    cat.Name,
    sch.LastRunTime,
    sub.LastStatus,
    sch.ScheduleID,
    u.UserName AS CreatedBy
FROM Subscriptions sub
    INNER JOIN Catalog cat
        ON sub.Report_OID = cat.ItemID
    INNER JOIN ReportSchedule rs
        ON sub.SubscriptionID = rs.SubscriptionID
    INNER JOIN Schedule sch
        ON rs.ScheduleID = sch.ScheduleID
    LEFT OUTER JOIN Users u
        ON sch.CreatedById = u.UserID
ORDER BY cat.Name

accessing the database directly like this [is not supported](https://msdn.microsoft.com/en-AU/library/ms156016.aspx) — Fowl, Sep 22 '16 at 03:14
@Fowl That's correct, but considering this is a 5.5-year-old read-only query that still works fine, you're probably safe to run this on a one-off basis to quickly look up a subscription ID. :) — db2, Sep 22 '16 at 12:21

score 0 · Answer 2 · answered Sep 22 '16 at 03:21

Using the RS.exe utility with a short "rss" script that uses the SetScheduleProperties method you could alter a "Once" type shared schedule into the immediate future.

Unfortunately there doesn't seem to be a API method to trigger a schedule directly.

Can I execute an SSRS report from a windows batch file?

2 Answers2