Debugging "Incorrect Syntax" Exception

Question

I have recently been working with a COM+ component that processes an input XML file, and makes a number of database updates based on the supplied data.

Running SQL Profiler with EventClass Exception, and User Error Message selected, I see:

Exception Error: 102, Severity: 15, State: 1
User Error Message Incorrect syntax near '3'.

However I really want to see the full SQL that is being supplied by the COM+ component.

Is there anyway for me using Profiler, or other, for me to intercept the SQL statement that was sent to the SQL Server?

This machine is using SQL Server 2005, and the COM+ object is written in Delphi.

Answer 1

In Profiler, try watching the events SP:StmtStarting and SQL:StmtStarting, and include column TextData in the output.

I think those events are only shown in the pick list if you select the "Show All Events" checkbox on the Events Selection tab.

Answer 2

The events needed are SQL:BatchStarting or SQL:BatchCompleted. The main confusing point is that the SQL is output AFTER the error messages - which does make sense when you think about it, but may not be intuitive.

As an example, with the query SELECT * FROM Table WHERE ID=3 8 (note the incorrect space between 3 and 8), I received the following output.

Exception   Error: 102, Severity: 15, State: 1
User Error Message  Incorrect syntax near '8'.
SQL:BatchStarting   SELECT * FROM Table WHERE ID=3 8
SQL:BatchCompleted  SELECT * FROM Table WHERE ID=3 8