Home / server / Questions / 242901
In Process
Christine
Asked: 2011-03-04 11:49:17 +0800 CST

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

  • 772

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...

ssrs-2008-r2

2 Answers

  1. 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
    • 0

  2. 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.

    • 0