CODEWITHSUNDEEP
sql-serversql-server-profiler
Tony_Henrich
Tony_Henrich

Reputation: 44205

SQL Server profiler not filtering by TextData column filter

I have a stored procedure, let's call it 'MyStoredProc', which gets called every few seconds.

I want to filter it because it's cluttering the view.

The proc is called like exec sp_executesql N'Exec @Return = [dbo].[MyStoredProc]....

I already have two Not Like filters on TextData column which work fine for other texts. However this third one is not working. The calls are still showing.

I have entered different variations: %[MyStoredProc]% or %MyStoredProc% or MyStoredProc.

They don't work.
What's the proper syntax for this? Also can a TextData filter work on a variable name?

I am using Profiler 2014 targeting a 2012 engine.

Upvotes: 22

Views: 23121

Answers (4)

Ronald
Ronald

Reputation: 26

Try enabling the 'show all events' and 'show all columns' in the events selection tab. For me the filter on te TextData was also not working, until I enabled these settings. Very strange, because the only thing I expect these settings to do is show more complex events and columns.

Upvotes: 1

Martin
Martin

Reputation: 3472

As the filter did not work, i used SQL profiler > Menu > Edit > Find > "StoredProcName" to locate the rows containing it.

Upvotes: 0

RLH
RLH

Reputation: 15698

Not sure if this will help the OP, but I've ran into this scenario many times and simply had to "fiddle" around with various types of queries, export the data to Excel and then manually filter the results.

However, I have found a way to get around this that seems to work consistently. First, create a new Profiler Template (File>Templates>New Template...) Setup the template with all of the desired settings or use the option Base new template on existing one:. I know that I initially used this options for my templates and I think that could have been the problem.

Now, when you go to add new filters, you need to add them by editing the Template. I could not get my TextData filter to work if I updated the property values for the trace. Instead, go to File>Templates>Edit Templates... and select and update your template.

Save the template, close the Profiler and re-open the template. When you run the template, the filter should work.

I'm not sure if there is an easier way but I do know that, for a fact, I couldn't get my filter to work before I saved, closed and re-opened my template. I'm not sure what the issue is, but I guess SQL Server Profiler is caching some old filter values that aren't being updated with the TextData field. A restart of the application seems to be the only fix when this occurs.

Upvotes: 5

itzik Paz
itzik Paz

Reputation: 433

How Filtering String From profiler textData As you can see, you should put the procedure name (I recommend witout the schema name , dbo.) between the %% (without the Brackets)

Upvotes: 17

Related Questions