Applies to
Problem description
When using the Extended Events technology for data collection, failed user queries can be captured, and will be shown in reports with the success status. The article will explain how to set auditing configuration to be able to recognize the quality of such events in reports.
Cause
When a stored procedure is executed the Extended Event technology will not have the capability to log the query under that stored procedure into session. Instead, it can only write the store procedure execution event, i.e. the EXEC command. As such, the technology doesn’t provide the execution status in the session log so whether the query was executed successfully or failed due to execution denial or incorrect value reference, the status will remain unknown. The same applies to any kind of parameterized query. These session entries are written in the ApexSQL central repository database as such and, inherently, the execution status remains undefined and is treated with the NULL value:
With the ApexSQL Audit reporting module, it was chosen to show this status with the Success flag with the interpretation that the stored procedure was actually executed on the SQL Server, disregarding that the query within it was executed with errors:
Resolution
Since the status for the described events can be ambiguous when found in the audit reports it would be much clearer for interpretation if the accompanying error is captured also with the said event. The event and the error will have similar, consecutive timestamps which will show the whole picture about what happened with the event:
To enable this solution, go to the Configure tab and select a database to update auditing settings. From the operations tree, under the Error branch, tick the User error message item, and confirm the update with the Apply button:
Where applicable, it is also recommended to switch to the SQL Audit data collection technology (supported with SQL Server 2017 and later) as it doesn’t have described limitations.
