How to catch erroneous SQL operator

Question

We have 3-rd party application. In one of the reports it gives us error message:
Microsoft OLE DB Provider for SQL Server: Subquery returned more than 1 value. This is not permitted...
I need to find which statement causes this error. The report duration is quite long - about several hours. I want to setup extended event session to catch this error. Am I right? Are there any other ways (except xEvents and trace) to grab this erroneous SQL statement ?

Answer 1

In this instance, if you wanted to use XE you can grab it through "sqlserver.error_reported". The error number for a subquery returning more than one value is 512 - so we can filter on ONLY error numbers of 512.

XE Session Code:

CREATE EVENT SESSION [Error512Tracking] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([error_number]=(512))) 
ADD TARGET package0.event_file(SET filename=N'C:\ExtendedEvents\Error512Tracking.xel',metadatafile=N'C:\ExtendedEvents\Error512Tracking.xem')