Feeds:
Posts
Comments

Posts Tagged ‘Reporting Services’

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.

Read Full Post »

The previous post showed a sql script that is useful for finding all your Reporting Services jobs. Now that you have that information, lets say you’d like to kick off a scheduled report that either already ran, or you’d like to run again but you don’t want to have to step through all the settings for that subscription in the Reporting Services web interface. Instead you can use the Job ID that you got in the previous result set and use it to start the report job. I use this script every month to manually kick off a scheduled report that generates client invoices.


/*  Report Server Rerun subscription  */

--Execute Monthly Web Statement Subscripion

EXEC msdb..Sp_start_job @job_name = ‘A251BE76-0453-4A8F-8C98-69439B881B23’

--Client Holdings

EXEC msdb..Sp_start_job @job_name = ’57AD46EF-A46F-41A7-83F3-9430B51CEDE8′

--Standard subscription

EXEC msdb..Sp_start_job @job_name = ‘DC1C9E50-D18E-4A69-9452-2274D21232D7’

Read Full Post »