Sql-server – Profiler: Only want to see Provider Errors

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
            ( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
    -- about 700 lines of logic, math and if-parameter-this-then-that
    -- stuff omitted
      INSERT InnerProcedureCallWithinLoop__TraceTable
              VALUES (@from_locn, @About53PARAMSOMITTED
      EXEC @ConsiderItem =
            InnerProcedureCallWithinLoop
                  @from_locn,
                        @About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.

These are the messages that get logged:

16943 Could not complete cursor operation because the table schema changed after the cursor was declared.
19052 The active trace must be stopped before modification.

So it seems like this is an inherent issue with creating a trigger on the trace table (and I did not crack open a debugger to see exactly what Profiler is trying to do in this case). This has nothing to do with the fact that you're pulling spid values in the trigger, or using DBCC INPUTBUFFER, or using dynamic SQL. A simple trigger that just does PRINT N'hi'; also causes the same symptom: those two exceptions, the error dialog in Profiler, and the trace stops logging to that table altogether.

Perhaps you can just set up a trace that collects both the sort warning events and SQL:StmtCompleted and try to manually correlate them using sequence / transaction info or simple intuition. Or you could try my idea from above:

You can always automate pulling in trace data to a table from a file once a server-side trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved.

It's not exactly as simple as it sounds, and it would have to poll quite frequently in order for the trigger to rely on the timeliness of subsequent inputbuffer data. I don't want to get too deep into the implementation details of that, it's just an off-the-cuff idea.

The other idea is to upgrade. :-)