Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.