Snippet Name: SSRS_Subscription_Email_Error

Tags:

Created Date: 2025-06-16 13:12:45
Last Modified Date: 2025-05-31 10:31:49

Dependencies:

DBA Notes:

Sample Output:

Title: SSRS Subscription Email Error
Author: user
i have an SSRS report that has multiple subscriptions tied to it. one of the subscriptions is returning the error Failure sending mail: The permissions granted to user 'CIGFINANCIAL\SKhandelwal' are insufficient for performing this operation.Mail will not be resent.

Author: ChatGPT
The error you're encountering indicates that the user `'CIGFINANCIAL\SKhandelwal'` does not have sufficient permissions to send the email for that specific subscription in SSRS.

Here's how you can troubleshoot and resolve this issue:

### 1. **Check User Permissions in SSRS**
   - Ensure that `'CIGFINANCIAL\SKhandelwal'` has the appropriate permissions in **SQL Server Reporting Services (SSRS)**. Specifically, the user should have at least **"Browser"** role access to the folder or report and permissions to manage subscriptions if applicable.
   - Navigate to the SSRS web portal:
     - Go to the report or folder where the report is stored.
     - Click on **Manage** > **Security**.
     - Ensure that `'CIGFINANCIAL\SKhandelwal'` is part of a role with the correct permissions.
   
### 2. **Check Email Settings for SSRS**
   - Verify that the **SMTP server settings** in the SSRS configuration are correct. Sometimes, incorrect or restricted SMTP settings can cause email-sending errors.
     - Open the `RSReportServer.config` file and confirm the `<SMTPServer>` and other mail settings are correctly configured.
     - This file is usually located in `C:\Program Files\Microsoft SQL Server\MSRS[Instance_Name]\Reporting Services\ReportServer`.

### 3. **Subscription Ownership**
   - Confirm that `'CIGFINANCIAL\SKhandelwal'` is the owner of the subscription in question. If the subscription was created by someone else, it could cause permission issues.
     - To check this, you can run a query on the `ReportServer` database:
       ```sql
       SELECT SubscriptionID, OwnerID, Path, Report_Owner.UserName AS ReportOwner, Sub_Owner.UserName AS SubscriptionOwner
       FROM Subscriptions s
       JOIN Catalog c ON s.Report_OID = c.ItemID
       JOIN Users Report_Owner ON c.OwnerID = Report_Owner.UserID
       JOIN Users Sub_Owner ON s.OwnerID = Sub_Owner.UserID
       WHERE Report_Owner.UserName = 'CIGFINANCIAL\SKhandelwal'
       AND Path LIKE '%<report_name>%';
       ```
     - Make sure the owner of the subscription has adequate permissions.

### 4. **Permissions in the ReportServer Database**
   - If you're working directly with the `ReportServer` database and handling subscriptions programmatically, ensure that the SSRS job for this subscription has the necessary SQL permissions.

Once you verify these configurations, you can retry the subscription. If the issue persists, you might want to check the Windows Event Viewer for any additional errors logged around the time the subscription fails.

Author: user
in  ssrs , how can i join the ExecutionLog2 tot eh subscription

Author: ChatGPT
In **SQL Server Reporting Services (SSRS)**, the `ExecutionLog2` view stores log information about report executions, while subscriptions are stored in the `Subscriptions` table. To join them, you need to link the report execution with the corresponding subscription execution.

Here???s how you can create a query that joins `ExecutionLog2` with the `Subscriptions` table:

### Tables and Views to Use:
- **`ExecutionLog2`**: Contains details about report executions (including subscriptions).
- **`Subscriptions`**: Stores details about the subscriptions.
- **`Catalog`**: Links the report being executed to the subscription.
- **`Users`**: For subscription owner information.

### Joining Logic:
- The `ExecutionLog2` view contains a `ReportID` column that can be joined with the `ItemID` in the `Catalog` table to identify which report was executed.
- The `SubscriptionID` in `ExecutionLog2` can be joined with the `SubscriptionID` in the `Subscriptions` table to find the corresponding subscription.
- The `Subscriptions` table also contains the `OwnerID`, which can be linked to the `Users` table to find the user who owns the subscription.

### Query Example:

```sql
SELECT 
    el.InstanceName,
    el.ReportID,
    c.Name AS ReportName,
    el.UserName AS ExecutedBy,
    el.TimeStart,
    el.TimeEnd,
    el.Status,
    el.ByteCount,
    el.[RowCount],
    el.Source AS ExecutionSource,
    sub.Description AS SubscriptionDescription,
    sub.LastRunTime AS SubscriptionLastRunTime,
    u.UserName AS SubscriptionOwner
FROM 
    ExecutionLog2 el
    LEFT JOIN Catalog c ON el.ReportID = c.ItemID
    LEFT JOIN Subscriptions sub ON el.SubscriptionID = sub.SubscriptionID
    LEFT JOIN Users u ON sub.OwnerID = u.UserID
WHERE 
    el.SubscriptionID IS NOT NULL -- Only include subscription executions
ORDER BY 
    el.TimeStart DESC;
```

### Explanation:
- **`ExecutionLog2 el`**: Retrieves execution data, filtered to only include executions related to subscriptions (`el.SubscriptionID IS NOT NULL`).
- **`Catalog c`**: Joins on `el.ReportID = c.ItemID` to get the report name.
- **`Subscriptions sub`**: Joins on `el.SubscriptionID = sub.SubscriptionID` to fetch details about the subscription.
- **`Users u`**: Joins on `sub.OwnerID = u.UserID` to get the user who owns the subscription.

### Important Columns:
- `el.TimeStart` and `el.TimeEnd`: Execution start and end times.
- `el.Status`: Indicates the status of the report execution (e.g., success or failure).
- `sub.LastRunTime`: Shows when the subscription was last executed.
- `u.UserName`: The owner of the subscription.

### Filtering for Specific Subscriptions:
If you want to filter by a specific report or subscription, you can modify the `WHERE` clause:

```sql
WHERE 
    el.SubscriptionID IS NOT NULL 
    AND c.Name = 'YourReportName' -- Filter by report name
```

This query will give you insights into subscription-related executions, linking the execution log with subscription details.

Author: user
your join criteria, and some columns, do not exist or are incorrect. below is an actual working query that retrieves the data :  

SELECT 
    el.InstanceName,
    c.Name AS ReportName,
    el.UserName AS ExecutedBy,
    el.TimeStart,
    el.TimeEnd,
    el.Status,
    el.ByteCount,
    el.[RowCount],
    el.Source AS ExecutionSource,
    sub.Description AS SubscriptionDescription,
    sub.LastRunTime AS SubscriptionLastRunTime,
    u.UserName AS SubscriptionOwner
FROM [ReportServer].dbo.Catalog c 
  INNER JOIN [ReportServer].dbo.Subscriptions sub ON sub.report_oid = c.itemid 
  INNER JOIN [ReportServer].dbo.Users u ON sub.OwnerID = u.UserID
  INNER JOIN [ReportServer].dbo.ExecutionLog2 el ON c.Path = el.ReportPath
WHERE 
    el.RequestType = 'Subscription'
    AND el.TimeStart > DATEADD(dd,-1,getdate())
ORDER BY 
    el.TimeStart DESC;

Author: ChatGPT
User is working with SSRS report execution logs and subscriptions, retrieving data using the ExecutionLog2 view and joining with the Catalog, Subscriptions, and Users tables.

Author: tool
Model set context updated.

Author: ChatGPT
Thank you for sharing the corrected query! It looks like you're joining on the `ReportPath` in the `ExecutionLog2` view rather than the `ReportID`, and you're using the `RequestType = 'Subscription'` to filter for subscription executions. This is a more precise approach for your scenario. Here's the structure of your query for clarity:

- **`Catalog`**: Provides report details using `itemid` for joining with `Subscriptions`.
- **`Subscriptions`**: Contains subscription metadata, linked via `report_oid`.
- **`Users`**: Links the owner of the subscription to `Subscriptions` via `OwnerID`.
- **`ExecutionLog2`**: Logs report executions, with `ReportPath` being used to link to the report.

This query will pull execution data for subscriptions within the last day, ordered by the start time.

Let me know if you need further adjustments!