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?

Improve this question
2
  • What version of SQL Server are you on? Extended Events has a much more flexible filtering architecture. Commented Mar 4, 2011 at 22:32
  • This particular instance is SQL Server 2005 based Commented Mar 4, 2011 at 22:53

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
When I tried this I got the exceptions but none of the SQL:BatchCompleted or RPC:Completed in my trace?
I also recommend adding User Error Message (to see detailed descriptions of the errors); and filter out Error 0, 5701 and 5703 (those last two codes are unimportant notifications)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.