Feeds:
Posts
Comments

Archive for the ‘Reporting Services’ Category

Once you have kicked off your report subscription you might want to see the status. Again, if you don’t want to log onto the SQL Server Reporting Services web interface you can do this with a simple SQL Query.


/*Active (Running) subscriptions*/

SELECT

c.name,

rs.scheduleid AS jobid,

sub.DESCRIPTION,

sub.laststatus

FROM reportserver..catalog c

INNER JOIN reportserver..subscriptions sub
ON (c.itemid = sub.report_oid)

INNER JOIN reportserver..activesubscriptions asub
ON (sub.subscriptionid = asub.subscriptionid)

INNER JOIN reportserver..reportschedule rs
ON (sub.subscriptionid = rs.subscriptionid)

This query will return the Report Name, the JobID, description and the LastStatus.

Advertisements

Read Full Post »

This script is a useful one that I have at my office.  I use it often to find all the SQL Server Reporting Services scheduled reports that exist on a SQL Reporting Server instance.  It list the job name, job description, the scheduled date and time and the status of the last time it was run.


/*Information about the subscriptions on your Report Server*/

SELECT c.name,

       rs.scheduleid as JobID,

       sub.DESCRIPTION,

       sub.laststatus,

       sch.lastruntime,

       sjs.next_run_date,

       sjs.next_run_time

FROM   reportserver..catalog c WITH(nolock)

       INNER JOIN reportserver..subscriptions sub WITH(nolock)

         ON ( c.itemid = sub.report_oid )

       INNER JOIN reportserver..reportschedule rs WITH(nolock)

         ON ( c.itemid = rs.reportid

              AND sub.subscriptionid = rs.subscriptionid )

       INNER JOIN reportserver..schedule sch WITH(nolock)

         ON ( rs.scheduleid = sch.scheduleid )

       INNER JOIN msdb..sysjobs sj WITH(nolock)

         ON ( CAST(rs.scheduleid AS SYSNAME) = sj.name )

       --sysname equivalent to nvarchar(128)

       INNER JOIN msdb..sysjobschedules sjs WITH(nolock)

         ON ( sj.job_id = sjs.job_id )

       INNER JOIN msdb..sysschedules ss WITH(nolock)

         ON ( sjs.schedule_id = ss.schedule_id )

-- WHERE c.Name like ‘ISS %’
-- A good way to filter by report name.

ORDER  BY sub.DESCRIPTION 

Read Full Post »