Snippet Name: SSRS_Subscription_Report_Query

Tags:

Created Date: 2025-06-16 13:12:44
Last Modified Date: 2025-05-31 10:32:01

Dependencies:

DBA Notes:

Sample Output:

Title: SSRS Subscription Report Query
Author: user
In SQL server for Reporting Services, can you help me write a report to find any subscription that is disabled, or has a sinlge event schedule, or where it's matching job is disabled

Author: ChatGPT
To create a report in SQL Server Reporting Services (SSRS) for subscriptions that meet the following criteria:

1. Disabled subscriptions.
2. Subscriptions with a single event schedule.
3. Subscriptions where the matching SQL Agent job is disabled.

You can use the following SQL query to retrieve this data. This query uses the SSRS `ReportServer` database:

```sql
WITH SubscriptionDetails AS (
    SELECT 
        s.SubscriptionID,
        s.ReportID,
        c.Name AS ReportName,
        s.Description,
        s.LastStatus,
        s.LastRunTime,
        s.DeliveryExtension,
        CASE 
            WHEN es.ScheduleID IS NOT NULL THEN 'Shared Schedule'
            ELSE 'Individual Schedule'
        END AS ScheduleType,
        sj.enabled AS JobEnabled,
        CASE 
            WHEN sj.enabled = 0 THEN 'Disabled'
            ELSE 'Enabled'
        END AS JobStatus,
        es.ScheduleID,
        CASE 
            WHEN COUNT(es.ScheduleID) OVER (PARTITION BY s.SubscriptionID) = 1 THEN 'Single Event'
            ELSE 'Multiple Events'
        END AS ScheduleCount,
        s.ModifiedByID,
        u.UserName AS ModifiedBy
    FROM 
        Subscriptions s
    LEFT JOIN 
        ReportSchedule rs ON s.SubscriptionID = rs.SubscriptionID
    LEFT JOIN 
        [Catalog] c ON s.ReportID = c.ItemID
    LEFT JOIN 
        Schedule es ON rs.ScheduleID = es.ScheduleID
    LEFT JOIN 
        msdb.dbo.sysjobs sj ON CAST(s.SubscriptionID AS NVARCHAR(128)) = sj.name
    LEFT JOIN 
        Users u ON s.ModifiedByID = u.UserID
)
SELECT 
    SubscriptionID,
    ReportName,
    Description,
    LastStatus,
    LastRunTime,
    DeliveryExtension,
    ScheduleType,
    JobStatus,
    ScheduleCount,
    ModifiedBy
FROM 
    SubscriptionDetails
WHERE 
    JobEnabled = 0 -- Matching job is disabled
    OR ScheduleCount = 'Single Event' -- Single event schedule
    OR ScheduleType = 'Individual Schedule' -- Disabled subscription
ORDER BY 
    ReportName, LastRunTime DESC;
```

### Explanation of the Query:
1. **Common Table Expression (CTE): `SubscriptionDetails`**
   - Retrieves details about subscriptions, including associated jobs and schedules.
   - Links subscriptions to their associated SQL Agent jobs.
   - Identifies if a schedule is shared or individual and counts the number of schedules associated with each subscription.

2. **Filters in the `WHERE` Clause**
   - `JobEnabled = 0`: Fetches subscriptions where the SQL Agent job is disabled.
   - `ScheduleCount = 'Single Event'`: Filters subscriptions with only a single event schedule.
   - `ScheduleType = 'Individual Schedule'`: Captures subscriptions without a shared schedule.

3. **Output Columns**
   - Includes key details like subscription description, report name, last run time, and job status.

4. **Ordering**
   - Sorts results by report name and last run time (most recent first).

### Additional Notes:
- Replace `msdb.dbo.sysjobs` with the actual schema if your SSRS environment has a different configuration.
- Ensure you have the necessary permissions to query the `ReportServer` and `msdb` databases.

You can use this query in your SSRS report as a dataset and design the report to display the results in a table or another format. Let me know if you need help with report design!