SQL Server Performance

adhoc execution of a reporting service subscription

Discussion in 'SQL Server 2008 General DBA Questions' started by L0st_Pr0phet, Jul 13, 2010.

  1. L0st_Pr0phet Member

    Hi,
    Is there anyway to lauch a reporting service subscription via t-sql.
    I have a job that through out the day checks a folder, if a file is there it executes a SSIS package and then uses DB mail to email out the results. Doing it this way there are a few limitations to what you can do with the results in the attachment. I was wondering if I could setup a subscription for a report with all the recipients on and use the excel or pdf attachment options.
    Any thoughts?
    L0st_Pr0phet, Jul 13, 2010
    #2

  2. satya Moderator

    The SSRS subscripts are designed to execute on schedule and using SQLAgent jobs with TSQL you can achieve the manual subscription.
    In the past we have done such a solution for a customer (trying to find the code to get you) using a web service API call with .NET as CLR proc and then use that SP using SQL Agent job using the sp_start_job system stored procedure. So here is the trick part here you need the subscript GUID stored on SSRS subscription system tables, find out and refer to that corresponding SQL Server Agent job named using the same GUID.
    To find the subscription Agent jobs, open SQL Server Management Studio and connect to the relational instance hosting the report server content databases. Expand the SQL Server Agent jobs. Each job will have one step and will be scheduled to run at the time specified in Report Manager. Subscriptions have a TimedSubscription event type and reference the report?s ItemID key in the ReportServer database Catalog table.
    Also you can use @EventType parameter within SP_START_JOB as timed subscription pump out the required reports.
    satya, Jul 13, 2010
    #3

  3. L0st_Pr0phet Member

    cheers for that, hopefully I will be able to get something running.
    stew
    L0st_Pr0phet, Jul 14, 2010
    #4

  4. L0st_Pr0phet Member

    Yep managed to get a solution thanks, for the record
    1) create a reporting services report
    2) create a subscription that doesnt start for a while
    3) find the job created based on the date you told it to start.
    4) strip out the T-sql from this job and use this in your code
    5) delete the subscription job to tidy up!
    a bit of a bodge but works well enough.
    stew
    L0st_Pr0phet, Jul 14, 2010
    #5

Share This Page