Snippet Name: Error_Reporting_Procedure_Filter

Tags:

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

Dependencies:

DBA Notes:

Sample Output:

Title: Error Reporting Procedure Filter
Author: user
i have a SQL Server Extended Event, that is monitoring the parameters for a specific stored procedure. i tried adding another event to capture error_reported, but i don't seem to be able to limit teh errors to just that specific procedure. can you help? here is the code: CREATE EVENT SESSION [ProcedureParameterCapture] ON SERVER 
ADD EVENT sqlserver.error_reported,
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],N'DoRequestParameterAndOutputGeneration')))
ADD TARGET package0.ring_buffer(SET max_memory=(1024000),occurrence_number=(1000))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO




Author: ChatGPT
You're right that adding `sqlserver.error_reported` directly doesn't filter it to a specific stored procedure. Unlike `rpc_completed`, the `error_reported` event does not have a direct reference to `object_name`. 

### Solution:
You need to filter the `error_reported` event using the `attach_activity_id` action and the `activity_id` field, which allows you to correlate errors back to the session where the procedure was executed.

#### Updated Extended Event Session:
```sql
CREATE EVENT SESSION [ProcedureParameterCapture] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.username, sqlserver.attach_activity_id)
),
ADD EVENT sqlserver.rpc_completed(
    SET collect_statement=(1)
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.username, sqlserver.attach_activity_id)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],N'DoRequestParameterAndOutputGeneration'))
)
ADD TARGET package0.ring_buffer(SET max_memory=(1024000),occurrence_number=(1000))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=ON);
GO
```

### Explanation of Fix:
1. **Added `attach_activity_id` to both events**:  
   - This allows you to correlate the `error_reported` event to the `rpc_completed` event that executed the stored procedure.
   
2. **Enabled `TRACK_CAUSALITY=ON`**:  
   - This helps group related events under the same execution context.

### How to Analyze the Data:
When you query the captured events, use `activity_id` to match errors to procedure executions:
```sql
SELECT 
    event_data.value('(event/@name)', 'NVARCHAR(100)') AS event_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text,
    event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS activity_id
FROM 
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('ProcedureParameterCapture*.xel', NULL, NULL, NULL)
) AS xEvents
WHERE event_name = 'error_reported' OR event_name = 'rpc_completed'
ORDER BY activity_id, event_name;
```
This will show the errors that occurred within the same activity session as the stored procedure execution.

Would you like me to add any additional filters or refine the session further?

Author: user
i get an error Msg 25623, Level 16, State 3, Line 1
The event action name, "sqlserver.attach_activity_id", is invalid, or the object could not be found

Author: ChatGPT
The error indicates that "sqlserver.attach_activity_id" isn???t a valid action for the error_reported event. Unlike rpc_completed, error_reported doesn???t support that action for correlating events.

### Options to Consider

1. **Use a Custom Error Handler in Your Stored Procedure**  
   Modify your stored procedure to catch errors (using TRY...CATCH) and then explicitly log or raise errors with additional context (such as the procedure name). You could then capture these custom messages with your Extended Events session.

2. **Filter by Additional Context in Error Message**  
   If your errors contain the stored procedure name in their text (for example, if you include it in a custom error message), you could filter the error_reported event on that text using a predicate like:
   ```sql
   WHERE [sqlserver].[like_i_sql_unicode_string]([message], N'%DoRequestParameterAndOutputGeneration%')
   ```
   However, this relies on your error messages consistently including that string.

3. **Consider Using Session or Client Identifiers**  
   Another approach is to use the session_id or other client identifiers to correlate events between rpc_completed and error_reported, assuming both events share the same session context.

### Revised Example (Using Custom Error Message Filtering)

If you opt to include the procedure name in the error message (for example, by using RAISERROR in your CATCH block), you could write your Extended Event session like this:

```sql
CREATE EVENT SESSION [ProcedureParameterCapture] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name,
           sqlserver.session_nt_username,
           sqlserver.sql_text,
           sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([message], N'%DoRequestParameterAndOutputGeneration%'))
),
ADD EVENT sqlserver.rpc_completed(
    SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name,
           sqlserver.session_nt_username,
           sqlserver.sql_text,
           sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],N'DoRequestParameterAndOutputGeneration'))
)
ADD TARGET package0.ring_buffer(
    SET max_memory=(1024000), occurrence_number=(1000)
)
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=ON);
GO
```

### Summary

- The error is due to using an unsupported action (`attach_activity_id`) with error_reported.
- Instead, consider including identifiable text in your error messages, or use custom error handling to capture context.
- Ensure that any filtering or correlation you implement is based on data that the events actually provide.

Let me know if you'd like to explore any of these options in more detail or need further assistance!