Tag Archives: SSRS 2008 R2

Prevent A Same SSRS Report From Running Concurrently

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.

  1. Create a VB.Net Class Library Project and save it as SSRSLib.vb
  2. 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.
  3. Write a class to retrieve list of SSRS Active report jobs
    Public Class SSRSInstance
    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("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
    		dt.Rows.Add(dr)
    	Next job
    
    	Return dt
    End Function
    End Class
    
  4. Build your project. Since my assembly named SSRSLib so that build project will produce two dll files which are SSRSLib.dll & SSRSLib.XmlSerializers.dll
  5. 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.
Continue reading

Scheduling Excel Report With SSRS 2008 R2 And ASP.Net

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:

  1. Prepare the report you want to sent and deploy Report Server
  2. 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
  3. Continue reading

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.
Continue reading