Working with Trace Filters

I'm trying to create a SQL Server Profiler trace that lets me capture queries executed against a small subset of tables in my database. The trace works for a single table but doesn't work when I add multiple tables. Why?

Imagine you are using the Pubs database and want to track queries that reference the authors or publishers table. You don't want to see information for other tables. Except for the modifications you need to make to the filter to demonstrate how to capture events against two tables rather than one, let's keep things simple and use the standard trace definition. Profiler's Help file for filters says, "Use the wildcard character (%) and semicolons to include any series of characters. For example, SQL%;MS% specifies that all events beginning with SQL and all events beginning with MS will be included in the trace." So you should be able to use a filter such as %authors%;%publishers%, which Figure 1 shows, to capture any query that contains the word authors or the word publishers.

However, this filter doesn't give you the results you want when you run SELECT * FROM authors in the Pubs database. This trace doesn't capture any queries. Now, add a filter that looks like the one in Figure 2. Run either SELECT * FROM authors or SELECT * FROM publishers. The trace works as expected and captures each query. Run SELECT * FROM titles, and you'll see that the trace performs as expected: You don't see this query.

Profiler makes it hard for you to enter the string %authors%;%publishers% on just one line in the filter. Typically, the Profiler GUI automatically creates a new line when you type the semicolon character, as Figure 2 shows. However, Profiler lets you cut and paste a string with an embedded semicolon directly into the filter. That's how I generated the filter that Figure 1 shows.

The answer to this puzzling problem is obvious if you save the definition of the trace by using the Script Trace... option from the File menu. Here's an excerpt from the trace definition that works:

EXEC sp_trace_setfilter @TraceID, 1, 1, 6, N'%authors%'EXEC sp_trace_setfilter @TraceID, 1, 1, 6, N'%publishers%'

Here's an excerpt from the trace definition that doesn't work:

EXEC sp_trace_setfilter @TraceID, 1, 1, 6, N'%authors%;%publishers%'

Under the covers, Profiler simply runs system stored procedures that define a trace. These commands show the sp_trace_setfilter calls that Profiler uses to define the filters for a trace.

The trace example that works calls sp_trace_setfilter twice, while the example that doesn't work calls the stored procedure only once. SQL Server Books Online (BOL) doesn't mention a semicolon when describing how to use sp_trace_setfilter to define a trace. The Help information you get from the Profiler GUI is simply wrong. This problem took me 2 hours of head scratching to figure out; hopefully, this solution will save you the headache I experienced!