Some report could have quite long run more then some minutes. Usually due to heavy query or big content.
This blog will show how to prevent a specific SSRS report to have two or more report instances being run in same time.
So it applies only to each report you want to validate.
Creating Class Library
First, you need to make a class library either using VB.Net or C# and add a Web Reference to SQL Server Reporting Service API.
This class library is intended to list all active SSRS report jobs.
- Create a VB.Net Class Library Project and save it as
- Add Reporting Service Web Reference.
To do that, click Add Service Reference, Advanced, Add Web Reference.
Inside Url text box type
http://yourServIpAddr/ReportServer/ReportService2010.asmx for SSRS 2008 R2 version, click ‘go’ arrow icon.
Edit the Web Reference Name to your prefer i.e ReportService2010 and then click Add Reference button.
- Write a class to retrieve list of SSRS Active report jobs
Public Class SSRSInstance
Public Sub New()
Public Function getJobs() As DataTable
Dim dt As New DataTable()
Dim rs As New ReportingService2010()
rs.Credentials = New System.Net.NetworkCredential("yourUserId", "yourPwd")
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
- Build your project. Since my assembly named SSRSLib so that build project will produce two dll files which are SSRSLib.dll & SSRSLib.XmlSerializers.dll
- It is better to test your class Lib with Console Application before proceeding any further to know whether it contains error or success
Okay first step has done. The SSRS list jobs class library is created. Usually the class library created for SSRS called custom assembly.
As default SQL Server Reporting Service 2008 R2 has Scheduled Report Generation named Subscription. You can choose what report and when to generate it also delivery method.
However this feature is quite standard. We must supply report’s Parameter manually to Subscription. So that in this blog I will show you how to make more flexible to this kind of schedule using Visual Basic Asp.Net
The generated report will be on Excel format.
Here are step by step:
- Prepare the report you want to sent and deploy Report Server
- Browse the report via http://Your_serverip/ReportServer and get its exact Url for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render
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:
- 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.
- Open Jobs folder to see running Reports. Refresh it to see current jobs.
- Double click to see more details at each job
- 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.
Posted in ASP .Net, SQL Server, Visual Basic .Net
Tagged ASP .Net, CancelJob, List Running Jobs, List Running Reports, ListJobs, ReportingService2010, ReportService2010, SSRS 2008 R2, System.Net.NetworkCredential, Visual Basic .Net, WebService