Script to Delete Reporting Services Subscriptions

I wrote this script to delete all subscriptions against a reporting services report.

SQL 2000 offers fairly poor management tools for Reporting Services and I had a situation where automatically generated subscriptions were piling up on a 2000 box to the point where they could not be deleted manually.

To run - create a console program (note - console program) in Visual Studio and put the following code in a module. You will probably need to set up a few dependencies in the references.

I don't know if anyone will have as specific requirements as this but it might be a help if you are looking at programming against the reporting services object model.

Imports System
Imports System.Web.Services.Protocols
Imports RSManagement_Unsubscribe.ReportingServices

'Gerard Conlon 18 Sept 2008
'Program to delete all report subscriptions against a given ReportingServices report
'Written for compilition as a console application in Visual Studio


Module modRsManagement

   Sub Main()
       Dim strReportOwner As String
       Dim strReportPath As String

       Dim strParams() As String
       Dim i As Integer

       'the parameters are passed in the form of RSManagement_Unsubscribe.exe "ReportPath|ReportOwner
       'they are wrapped in double quotes and seperated by the pipe |
       'ie called from the command line as
       ' RSManagement_Unsubscribe.exe "/MyReportsDir/ReportName|MyServer\ASPNET"
       'ReportPath - full report path from the top level directory and including the report name
       'ReportOwner - usually the name of the server hosting reporting services and running under the ASPNET user ie MyServer\ASPNET"

       'Get the passed parameters
       If Len(Command()) > 0 Then
           strParams = Split(Mid$(Command(), 2, Len(Command()) - 2), "|")
           For i = 0 To UBound(strParams)
               If i = 0 Then strReportPath = strParams(0)
               If i = 1 Then strReportOwner = strParams(1)
           Next i
       End If


       'check to see if parameters are passed and if not give descriptive error
       If Trim(strReportPath) = "" Or Trim(strReportOwner) = "" Then
           Console.WriteLine("")
           Console.WriteLine("The Report Path and The Report owner must be specifed as parameters")
           Console.WriteLine("")
           Console.WriteLine("Parameters are seperated by the pipe char (|) and need to be in double quotes")
           Console.WriteLine("")
           Console.WriteLine("Example: RSManagement_Unsubscribe.exe " + """" + "/PrePostTimes/CMS Pretimes|AUS-DB1\ASPNET" + """")
           Console.WriteLine("")
           Console.WriteLine("Press any key to continue.......")
           Console.ReadLine() 'hold the screen open so message can be read

       Else
           Console.WriteLine(fDeleteSubscriptions(strReportPath, strReportOwner))

       End If

   End Sub


   Function fDeleteSubscriptions(ByVal sReport As String, ByVal sOwner As String) As String
       Dim rs As New ReportingService
       Dim subscriptions As Subscription() = Nothing
       Dim schedules As Schedule() = Nothing
       Dim i As Integer
       Dim cnt As Integer

       Console.WriteLine(" Searching for Subscriptions ..........")
       rs.Credentials = System.Net.CredentialCache.DefaultCredentials
       Console.WriteLine(sReport)
       Console.WriteLine(sOwner)
       rs.Timeout = 600000
       Try
           fDeleteSubscriptions = "0"
           cnt = 0
           Console.WriteLine("Deleting Subscription ..........")
           subscriptions = rs.ListSubscriptions(sReport, sOwner)
           'loop through and delete the subscriptions
           If subscriptions.GetLength(0) > 0 Then
               For i = 0 To subscriptions.GetLength(0) - 1
                   'rs.BeginListSchedules()
                   Console.WriteLine("Deleting " + subscriptions(i).Description)
                   rs.DeleteSubscription(subscriptions(i).SubscriptionID)

                   cnt = cnt + 1

               Next
               fDeleteSubscriptions = "Deleted " + CStr(cnt) + " Subscriptions"
           Else
               fDeleteSubscriptions = "Nil (0) Subscriptions"
           End If

       Catch e As SoapException
           fDeleteSubscriptions = fDeleteSubscriptions + (e.Detail.OuterXml)
       End Try
   End Function
End Module