SSRS 2008 R2 Programmatically List Running Reports/Jobs & Cancel Examples

SSRS 2008 R2 does not have pre built List Running Reports in Web format that comes from installation. Unlike the older version: SSRS 2005, we have to use SQL Server Management Studio (SSMS) to see SSRS 2008 R2 running reports / jobs .

The lack of this pre built web format is kind of missing in my day to day monitoring because of SSRS 2008 R2 List running jobs view in SSMS shows only the report name in the first look.
I have to look at each jobs properties to see more details which means more clicks are required.

The below screen shot shows the older version: SSRS 2005 list running Reports:

Steps to show SSRS 2008 R2 list running Reports with help of SSMS are:

  1. Open SSMS 2008 and connect to Reporting Service server.
    Set Server Type field to Reporting Service. Server Name set to Url http://<myserveripaddress>/reportserver. Replace the <myserveripaddress> to your report server ip address.
    Authentication, UserName and Passwd depend to your environment. If your localhost is Reporting Service server then you can use Window Auth.
    Otherwise if the server is a remote server then use basic/form auth. Anyway just try it.
  2. Open Jobs folder to see running Reports. Refresh it to see current jobs.
  3. Double click to see more details at each job
  4. Cancel Job(s) by right click job and click ‘Cancel Job(s)’

Alternatively, we can build our own list running reports/jobs tool. SSRS has a built in class to show running Reports programmatically. The class is ReportingService2010 for SSRS 2008 R2. This class is accessed via web service. The Url path of this webservice is http://<myserveripaddress>/ReportServer/ReportService2010.asmx.
In this blog post, I give you examples of programmatic SSRS 2008 R2 List running jobs.

Console Application Example

Please do as following:

  1. To use a ReportService2010 webservice you need to login with appropriate user credential in your code later. This step is necessary before you begin with your code.
    This user should have System Administrator Role in SSRS. Server User ‘Administrator’ already have System Administrator role so if you want to use ‘Administrator’ then you dont have to change anything only to know its password.
    If you want to use other user then you must to register it. To set this user credential, go to ‘Site Setting’ menu, ‘Security’ and click New Role assigment.
    Enter your Server username and set its role to ‘System Administrator’
  2. Create new console application in VB.Net mode. I use VS 2010 Express edition
  3. Add ReportService2010 Web service reference. Right Click your console project -> Add Web Reference.
    Dialog form will appear and put ReportService2010 web service Url: http://<myserveripaddress>/ReportServer/ReportService2010.asmx and click green arrow button.

    After entering username & password several times, this screen will appear

    Rename Web Service Name to ReportService2010.

  4. The source code:
    Sub Main()
           
    	ListJobSSRS()
    
    End Sub
    	
    Sub ListJobSSRS()
    	Dim rs As New ReportService2010.ReportingService2010()
    	rs.Credentials = New System.Net.NetworkCredential("<userid>", "<pwd>")
    	Dim jobs As Job() = Nothing
    	Try
    		jobs = rs.ListJobs()
    
    		ListRunningJobs(jobs)
    
    	Catch e As SoapException
    		Console.WriteLine(e.Detail.InnerXml.ToString())
    	End Try
    	Console.ReadLine()
    End Sub
    
    Function ListRunningJobs(ByVal jobs() As ReportService2010.Job) As Boolean
    	Dim runningJobCount As Integer = 0
    	Console.WriteLine("Current Jobs")
    	Console.WriteLine("================================" + Environment.NewLine)
    	Dim job As ReportService2010.Job
    	For Each job In jobs
    		
    		Console.WriteLine("--------------------------------")
    		Console.WriteLine("JobID: {0}", job.JobID)
    		Console.WriteLine("--------------------------------")
    		Console.WriteLine("Action: {0}", job.JobActionName)
    		Console.WriteLine("Description: {0}", job.Description)
    		Console.WriteLine("Machine: {0}", job.Machine)
    		Console.WriteLine("Name: {0}", job.Name)
    		Console.WriteLine("Path: {0}", job.Path)
    		Console.WriteLine("StartDateTime: {0}", job.StartDateTime)
    		Console.WriteLine("Status: {0}", job.JobStatusName)
    		Console.WriteLine("Type: {0}", job.JobTypeName)
    		Console.WriteLine("User: {0}" + Environment.NewLine, job.User)
    		runningJobCount += 1
    	   
    	Next job
    	Console.Write("There are {0} running jobs. ", runningJobCount)
    	
    End Function 'ListRunningJobs
    

    Replace <userid> & <pwd> with your report server username & password that I just explained earlier about report server credential.
    Basically, I create a new instance of ReportService2010.ReportingService2010() then assign this instance credential with network credential with help of System.Net.NetworkCredential() class.
    After that, I use ListJobs() function to list running jobs. With nice VS intellisense I can write many Properties of the report job.

Run this console app and the screen shot will look like this

Web Application Example

In this web example, I have ‘Cancel Selected Jobs’ button to cancel specific job so that related running report will break.
Please take a look of below screen shot of this example web app:

Create a new web project. After that add web reference by steps like above in console app.
Aspx file code (Default.aspx):

<form id="form1" runat="server">
    <div>
        <h3>SSRS 2008 R2 Running Reports List</h3>
        <asp:ListView ID="ListView1" runat="server" DataSourceID="ObjectDataSource1">
            <LayoutTemplate>
                <table width="90%">
                <thead>
                    <tr bgcolor="navy"><th>&nbsp;</th>
                    <th style="color:white">Name</th>
                    <th style="color:white">Computer Name</th>
                    <th style="color:white">User Name</th>
                    <th style="color:white">Action</th>
                    <th style="color:white">Start Time</th>
                    <th style="color:white">Status</th></tr>
                </thead>
                <asp:PlaceHolder ID="itemplaceholder" runat="server">
                </asp:PlaceHolder>
                </table>
                
            </LayoutTemplate>
            <ItemTemplate>
                <tr>
                <td><input type="checkbox" name="chkjobid" value='<%# Eval("JobID") %>' /></td>
                <td><%# Eval("Name") %></td>
                <td><%# Eval("ComputerName")%></td>
                <td><%# Eval("UserName")%></td>
                <td><%# Eval("Action")%></td>
                <td><%# Eval("StartTime")%></td>
                <td><%# Eval("Status")%></td>
                </tr>
            </ItemTemplate>
           
        </asp:ListView>
        <asp:Button ID="btncancel" Text="Cancel Selected Jobs" runat="server" />
        <asp:Label ID="debug_info" runat="server" Text=""></asp:Label>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="ssrslistjobs" SelectMethod="getJobs"></asp:ObjectDataSource>
    </div>
    </form>

I use listview to show list running Reports and ObjectDataSource as data container. Please see my previous post to know more about ListView and ObjectDataSource: How to Show Data from Any Source such as Array, DataTable, WebService and Database Using Asp.Net ListView and ObjectDataSource.

Add new Asp.Net Folder ‘App_Code’ and add new class, save it as “ssrslistjobs.vb”.

Imports Microsoft.VisualBasic
Imports ReportService2010
Imports System.Data
Public Class ssrslistjobs
    Public Sub New()

    End Sub
    Public Function getJobs() As DataTable
        Dim dt As New DataTable()
        dt.Columns.Add("JobID")
        dt.Columns.Add("Name")
        dt.Columns.Add("ComputerName")
        dt.Columns.Add("UserName")
        dt.Columns.Add("Action")
        dt.Columns.Add("StartTime")
        dt.Columns.Add("Status")

        Dim rs As New ReportingService2010()
        rs.Credentials = New System.Net.NetworkCredential("<your_userid>", "<your_pwd>")
        Dim jobs As Job() = Nothing
        jobs = rs.ListJobs()
        Dim job As Job
        Dim dr As DataRow
        For Each job In jobs
            dr = dt.NewRow()
            dr("JobID") = job.JobID
            dr("Name") = job.Name
            dr("ComputerName") = job.Machine
            dr("UserName") = job.User
            dr("Action") = job.JobActionName
            dr("StartTime") = job.StartDateTime
            dr("Status") = job.JobStatusName
            dt.Rows.Add(dr)
        Next job

        Return dt
    End Function
End Class

I imports ReportService2010 Namespace so that I dont have to write ReportService2010 repeatedly.
I retrieve job’s ID, Name, ComputerName / Reporting Service Server, User that execute report, Action, Start Time and Status in DataTable format.
All of these data showed on ListView via ObjectDataSource.

Code behind file (Default.aspx.vb)

Imports ReportService2010
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Private listjobcount As Integer
    Protected Sub btncancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncancel.Click
        Dim rs As New ReportingService2010()
        rs.Credentials = New System.Net.NetworkCredential("<your_userid>", "<your_pwd>")

        Dim jobid As String
        Dim job_ids As String = Request.Form("chkjobid")
      
        Dim arrjobid = Split(job_ids, ",")
        For i As Integer = LBound(arrjobid) To UBound(arrjobid)
            jobid = arrjobid(i)
            rs.CancelJob(jobid)
        Next
    End Sub

    Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles ObjectDataSource1.Selected
        listjobcount = CType(e.ReturnValue, DataTable).Rows.Count
        If listjobcount = 0 Then
            btncancel.Visible = False
            debug_info.Visible = True
            debug_info.Text = "No Running Jobs"
        Else
            btncancel.Visible = True
        End If
    End Sub
End Class

If checkboxes are checked and ‘Cancel’ button is clicked then selected checkboxes that contains Job ID value are submitted.
I have to use ReportingService2010 class instance and CancelJob(<jobid>) function to cancel a spesific job.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s