6

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?

Sebastian Zaklada
  • 2,308
  • 1
  • 14
  • 25

1 Answers1

11

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I knew it was too late for me to find the right solution... thanks, man! This was a great start and after some tweaking, I have just what I needed. Much appreciated. – Sebastian Zaklada Mar 04 '11 at 22:52
  • When I tried this I got the exceptions but none of the `SQL:BatchCompleted` or `RPC:Completed` in my trace? – leeand00 Jan 31 '14 at 19:15
  • I also recommend adding [User Error Message](https://msdn.microsoft.com/en-us/library/ms190953.aspx) (to see detailed descriptions of the errors); and filter out `Error` 0, 5701 and 5703 (those last two codes are unimportant notifications) – Euro Micelli Jul 15 '15 at 20:30