Logging erroneous queries only on SQL server

Question

I have what would seem to be an easy goal to accomplish, yet I have not found a good solution. Google does not shed a light on it and I just hope that I have been looking for a solution in wrong places or just tried to use tools in a wrong way...

Or perhaps it is already too late for me today to think clearly :) But this is where you can help me out, I hope.

I need to be able to log erroneous queries only which were executed on a specific instance of SQL Server. I thought that SQL Profiler would allow me to do it in no time, but I have not been able to find a combination of settings that would allow me to log only those queries, that returned an error (most likely parsing error) + error details.

Imagine SQL Server with 100s of queries being executed each second, and only 1 or 2 improperly formed queries each day resulting with a parsing error. I just need to be able to come and see those 1-2 queries per day without having to run a very expensive profile for all queries on the system and having to dig through it each day.

Is that possible at all?

Answer 1

Set up a trace for the Exception Event Class, SQL:BatchCompleted Event Class and RPC:Completed Event Class. Add a filter for Error not equal to 0. Start the trace. Done.