How to Identify Orphan Report Subscriptions

Issue
Orphaned report subscriptions are generating  "unable to render report" error messages within the EEM system log.
These are being reported from the ION EEM Subscription Monitor service.

Product
ION EEM

Environment
ION EEM 3.95 SP1
ION EEM 4.0, 4.0 R2
ION EEM 5.0

Cause
A report subscription is attempting to run a report that no longer exists.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Execute the following query to determine if a configured report subscription is missing the report that it is intended to run.
If the ReportID in RPT_Report Table column contains NULL, then it is an orphaned report subscription.

   USE ION_EEMData  
  SELECT rs.SubscriptionId AS [SubscriptionID in RPT_Subscriptions Table]  
         ,rs.ReportID AS [ReportID in RPT_Subscriptions Table]  
         ,r.ReportID AS [ReportID in RPT_Report Table]  
         ,r.DisplayName AS [Report Display Name in RPT_Report Table]  
    FROM RPT_Subscriptions rs  
    LEFT JOIN RPT_Report r ON rs.ReportID = r.ReportID  
    ORDER BY r.ReportID 

To remove the orphaned subscription record(s) from the RPT_Subscriptions table, execute the following query,
replacing x with the rs.SubscriptionId of the identified orphaned subscription in the preceding query:

   USE ION_EEMData  
  DELETE FROM RPT_Subscriptions rs
     WHERE rs.SubscriptionId = x

** see attachment containing the SQL script text

Released for: Schneider Electric Canada