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.

Implementing Class Lib in Report

Copy both your custom class lib dll files to SSRS bin folder. Mine is C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin folder.

After that, you need to edit config file, rssrvpolicy.config in order to apply above custom assembly.
Config path on my computer is C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config
Yours maybe slightly different depends on SQL Server Reporting installation path.
Add rssrvpolicy.config with following xml tags (ignore dots) that contains System.Data.dll, System.Xml.dll, SSRSLib.dll, SSRSLib.XmlSerializers.dll :

 <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission. ">
.....
	<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DataAccessGroupSQL" Description="Code Group for SQl Server Data Access">
		<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll" />
	  </CodeGroup>
	  <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="SystemXml" Description="Code Group for System XML">
		<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll" />
	</CodeGroup>
	<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="SSRSLib.XmlSerializers.dll" Description="SSRSLib.XmlSerializers.dll">
		<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\SSRSLib.XmlSerializers.dll" />
	</CodeGroup>
	<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="SSRSLib.dll" Description="SSRSLib.dll">
		<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\SSRSLib.dll" />
	</CodeGroup>
.....
</CodeGroup>

Basically, those lines register our Custom Assembly to SSRS. Note that PermissionSetName is set to FullTrust. Also note that each custom assembly Url is inside SSRS bin folder.
Save the Config File and Restart SSRS service.
There are two .Net libraries, System.Data & System.Xml. Both are needed due to our class lib and SSRS Web Reference use them.

Create one report to test our custom assembly and put one textbox only just to show all active reports separated by semicolon.
Inside the Report designer, Click Report Menu, Report Properties, Reference. Add four assemblies which are System.Data, System.Xml, SSRSLib, SSRSLib.XmlSerializers.

Then Go to Code section and write function:

Function getJobs() as String
Dim x As New SSRSLib.SSRSInstance()        
Dim res as String = ""
Dim dt As System.Data.DataTable = x.getJobs()
For Each dr As System.Data.DataRow In dt.Rows
	res = res  & dr("Name").ToString() & ";"
Next

return res
End Function

This function is used to retrieve all active report name separated by semicolon. Put the function on textbox Expression with =code.getJobs().
Run the report to test your work is good. Note that your running test report also appear on list.

Using Custom Assembly to Avoid Running Same Report More Than One Concurrently

To apply above validation please follow below steps:

  1. Add reference to two internal assembly which are System.Data, System.Xml and your custom assemblies which are SSRSLib.dll, SSRSLib.XmlSerializers.dll

       

  2. Write function
    function isReportActive(byval ReportName as string) as boolean
    Dim x As New SSRSLib.SSRSInstance()
            
    Dim res as String = ""
    Dim countActive as Integer = 0
    Dim dt As System.Data.DataTable = x.getJobs()
    For Each dr As System.Data.DataRow In dt.Rows
        res  = dr("Name").ToString()
    	if ReportName = res then
    	countActive = countActive + 1
    	end if		
    Next
    
    if countActive >= 2 then
    return true
    else
    return false
    end if
    end function
    

    This function checks for same ReportName. If it is found then countActive is sum up by one.
    If countActive value is more then two then it indicates another instance of same report currently active. Could be run by other user.
    Why is condition of countActive variable compared with greater or same than two (countActive>= 2) ?
    It because your current report instance is counted also.

  3. Create a New Data Set with proper DataSource and write expression as below

    =iif(Not code.isReportActive(Globals!ReportName), "select EmployeeID, VendorID, OrderDate, TotalDue from Purchasing.PurchaseOrderHeader where OrderDate between '" + Parameters!startdate.Value + "' and '" + Parameters!enddate.Value + "'",
    "select '' as EmployeeID, '' as VendorID, '' as OrderDate, '0' as TotalDue")

    Above expression means if IsReportActive is false then run PurchaseOrderHeader query with filled parameters otherwise we just print empty data.
  4. Make simple table layout and textbox (name: validationmsg) to prompt an error message if validation is false. I made its font bold and red.

  5. Right Click validationmsg textbox, click Properties and go to Visibility tab. Check on Expression Radio Button and write below code
    =iif(Not code.isReportActive(Globals!ReportName), true, false)
    So this textbox only appear only if validation is false.
  6. Test it by preview the report with both scenario i.e without same report run and there is another instance running.

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