Viewing failed SQL statements in MS SQL 2008

Question

Is it possible to view the SQL statements that fail to either parse correctly (such as badly formed dynamically created queries which don't escape reserved characters) or have problems in their execution (such as cast errors) in SQL 2008 Profiler. If so what events should I be looking for?

See http://stackoverflow.com/questions/5199812/logging-erroneous-queries-only-on-sql-server/5199933#5199933. This is a heavyweight trace though as the events still need to be processed even though most will get filtered out. — Martin Smith, Aug 31 '11 at 10:19
This looks just what I wanted. However, even with the filters either error <> 0 or error >=1 I am getting a number of events which have null values as errors. Is it possible to filter them out based on filter criteria. — AlexC, Aug 31 '11 at 10:46
There is a checkbox in the Profiler UI when setting filters to do exactly this. Can't remember exactly what the text is. — Martin Smith, Aug 31 '11 at 10:47

score 2 · Accepted Answer · answered Sep 01 '11 at 19:38

After further research it seems better to use the sp_trace commands rather than profiler due to efficiency. I have created a trace using this script to find the SQL that is badly formed.

declare @trace_id INT  

exec sp_trace_create @traceid = @trace_id  OUTPUT,        
                @options =  2,       
                @tracefile =  N'c:\trace_xxx' -- change filename to one that makes better sense

select @trace_id AS trace_id

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 10
          , @columnid = 1
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 12
          , @columnid = 1
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 33
          , @columnid = 1
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 10
          , @columnid = 14
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 12
          , @columnid = 14
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 33
          , @columnid = 14
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 10
          , @columnid = 15
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 12
          , @columnid = 15
          , @on = 1

exec sp_trace_setevent @traceid = @trace_id
          , @eventid = 33
          , @columnid = 15
          , @on = 1


exec sp_trace_setfilter @traceid = @trace_id
          , @columnid = 31
          , @logical_operator = 0
          , @comparison_operator = 2
          , @value = 0

exec sp_trace_setfilter @traceid = @trace_id
          , @columnid = 1
          , @logical_operator = 0
          , @comparison_operator = 7
          , @value = N'exec sp_reset_connection'



exec sp_trace_setstatus @traceid =  @trace_id , @status = 1

Viewing failed SQL statements in MS SQL 2008

1 Answers1