Snippet Name: Failed_Reports_Failure_Reason

Tags:

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

Dependencies:

DBA Notes:

Sample Output:

Title: Failed Reports Failure Reason
Author: user
for SQL Server 2019, i have the following query which finds recent reports that failed, and their status. is the reason for the failure available to add to this SLQ Server Query?  SELECT '[Failed Reports]' AS [Failed Reports],
[ex].[Status] + ' Today' AS Focus,
  --@Layer AS ServerLayer,
  CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
  CONVERT(VARCHAR(128),DB_NAME()) AS DatabaseName,  
  a.name, 
  DATEDIFF(minute,[ex].TimeStart,[ex].TimeEnd) AS ElapsedTime,
  [ex].[Status],
  [ex].[RequestType],
  [ex].[Format],
  [ex].[Parameters],
  [ex].[ReportAction],
  [ex].[TimeStart],
  [ex].[TimeEnd]
FROM [ReportServer].dbo.[ExecutionLog2] ex
  INNER JOIN [ReportServer].[dbo].[Catalog] a
    ON ex.[ReportPath] = a.[Path]
WHERE [RequestType] ='Subscription'
  AND [TimeStart] > DATEADD(dd,-1,CONVERT(DATE,getdate()))
  AND [ex].[TimeStart] >=@dt
 AND [ex].[Status] <> 'rsSuccess' --'rsProcessingAborted'
 ORDER BY [ex].[TimeStart] DESC

Author: ChatGPT
Yes, you can add the failure reason to your SQL Server query by including the `ByteCount` and `RowCount` columns from the `[ReportServer].dbo.[ExecutionLog2]` table. These columns provide additional details about the execution results. However, to get more specific error details, you should look at the `AdditionalInfo` column, which stores XML data containing detailed error messages.

Here's how you can modify your query to include the error details:

```sql
SELECT 
    '[Failed Reports]' AS [Failed Reports],
    [ex].[Status] + ' Today' AS Focus,
    CONVERT(VARCHAR(128), @@SERVERNAME) AS ServerName,
    CONVERT(VARCHAR(128), DB_NAME()) AS DatabaseName,  
    a.name, 
    DATEDIFF(minute, [ex].[TimeStart], [ex].[TimeEnd]) AS ElapsedTime,
    [ex].[Status],
    [ex].[RequestType],
    [ex].[Format],
    [ex].[Parameters],
    [ex].[ReportAction],
    [ex].[TimeStart],
    [ex].[TimeEnd],
    [ex].[ByteCount],
    [ex].[RowCount],
    TRY_CONVERT(XML, [ex].[AdditionalInfo]).value('(ReportServerMessage)[1]', 'NVARCHAR(MAX)') AS FailureReason
FROM 
    [ReportServer].dbo.[ExecutionLog2] ex
INNER JOIN 
    [ReportServer].[dbo].[Catalog] a
    ON ex.[ReportPath] = a.[Path]
WHERE 
    [RequestType] = 'Subscription'
    AND [TimeStart] > DATEADD(dd, -1, CONVERT(DATE, GETDATE()))
    AND [ex].[TimeStart] >= @dt
    AND [ex].[Status] <> 'rsSuccess' --'rsProcessingAborted'
ORDER BY 
    [ex].[TimeStart] DESC;
```

### Explanation of the changes:
1. **`ByteCount` and `RowCount`**: These columns provide information about the data processed during the report execution.
2. **`AdditionalInfo` XML Parsing**:
   - The `AdditionalInfo` column contains XML details, which include potential error messages.
   - Using `TRY_CONVERT(XML, [ex].[AdditionalInfo])` ensures that the query does not fail if the data is not in valid XML format.
   - The `.value('(ReportServerMessage)[1]', 'NVARCHAR(MAX)')` extracts the error message from the XML.

This should help you determine the reason for report failures within the last day. Let me know if you need any adjustments!

Author: user
it appears that the output of the xml column doe not contain the tag we were searching for: TRY_CONVERT(XML, [ex].[AdditionalInfo]).value('(ReportServerMessage)[1]', 'NVARCHAR(MAX)') AS FailureReason

Author: ChatGPT
If the `AdditionalInfo` column doesn't contain the expected `<ReportServerMessage>` tag, it's important to inspect its actual structure. You can retrieve a sample of the XML content to analyze its tags and find where error messages might be stored.

### Step 1: Inspect the XML Structure
Run the following query to get a sample of the XML content:

```sql
SELECT TOP 10
    CONVERT(VARCHAR(MAX), [AdditionalInfo]) AS AdditionalInfo
FROM [ReportServer].dbo.[ExecutionLog2]
WHERE [AdditionalInfo] IS NOT NULL
  AND [Status] <> 'rsSuccess';
```

Once you have the structure, look for common tags such as:

- `<Messages>` or `<Message>`
- `<Error>`
- `<Exception>`
- `<Details>`

### Step 2: Adjust the Query Based on Actual Tags
After identifying the correct tag, adjust the query like this:

```sql
SELECT 
    '[Failed Reports]' AS [Failed Reports],
    [ex].[Status] + ' Today' AS Focus,
    CONVERT(VARCHAR(128), @@SERVERNAME) AS ServerName,
    CONVERT(VARCHAR(128), DB_NAME()) AS DatabaseName,  
    a.name, 
    DATEDIFF(minute, [ex].[TimeStart], [ex].[TimeEnd]) AS ElapsedTime,
    [ex].[Status],
    [ex].[RequestType],
    [ex].[Format],
    [ex].[Parameters],
    [ex].[ReportAction],
    [ex].[TimeStart],
    [ex].[TimeEnd],
    [ex].[ByteCount],
    [ex].[RowCount],
    TRY_CONVERT(XML, [ex].[AdditionalInfo]).value('(//ErrorMessage)[1]', 'NVARCHAR(MAX)') AS FailureReason
FROM 
    [ReportServer].dbo.[ExecutionLog2] ex
INNER JOIN 
    [ReportServer].[dbo].[Catalog] a
    ON ex.[ReportPath] = a.[Path]
WHERE 
    [RequestType] = 'Subscription'
    AND [TimeStart] > DATEADD(dd, -1, CONVERT(DATE, GETDATE()))
    AND [ex].[TimeStart] >= @dt
    AND [ex].[Status] <> 'rsSuccess'
ORDER BY 
    [ex].[TimeStart] DESC;
```

**Adjust the XPath expression (`//ErrorMessage`)** to match the correct error tag found in your XML data.

### Step 3: If XML Tags Are Still Unknown
If the XML structure remains unclear, you can extract the raw content and review it manually:

```sql
SELECT 
    [ex].[Status], 
    CONVERT(VARCHAR(MAX), [ex].[AdditionalInfo]) AS AdditionalInfo
FROM [ReportServer].dbo.[ExecutionLog2] ex
WHERE [ex].[Status] <> 'rsSuccess'
  AND [ex].[AdditionalInfo] IS NOT NULL
ORDER BY [ex].[TimeStart] DESC;
```

Once you identify the relevant tag, refine the query to extract failure reasons accordingly.

Let me know if you need help analyzing the output.