18

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.

rene
  • 41,474
  • 78
  • 114
  • 152
Tony_Henrich
  • 42,411
  • 75
  • 239
  • 374
  • 3
    This is a question about how to use a tool used by programmers and shouldn't be closed with the given reason. The Op gave all the info there is needed to answer this. Reopen votes please. – rene Dec 30 '14 at 20:18
  • 1
    I couldn't reproduce the problem (`MyStoredProc` is wrong and so is `%[MyStoredProc]%` [due to the way (NOT) LIKE interprets brackets](http://stackoverflow.com/questions/13198870/a-couple-of-basic-sql-profiler-questions), but `%MyStoredProc%` should work). My setup is slightly different (both Profiler and engine are SQL2014) but I'd be surprised if that made any difference. Please put the complete SQL statement in your question, I assume there is a `DECLARE @Return` in there too? Can you reproduce the problem by executing the exact same SQL statement manually from SQL Server Management Studio? – Ruud Helderman Jan 01 '15 at 12:03

4 Answers4

14

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)

itzik Paz
  • 403
  • 5
  • 14
  • 4
    I have mentioned in my question that this didn't work. – Tony_Henrich Dec 26 '14 at 23:40
  • 1
    @Tony, that's the correct syntax, so there must be something else causing your problem. The recommendation to do it without schema name is sound too, because sometimes the textdata will appear like `...[dbo].[MyStoredProc]...`. Be careful of using square brackets in this context (e.g. `%[MyStoredProc]%`) because they have a special meaning for T-SQL LIKE clauses. You can instead use underscores. – OutstandingBill Jun 02 '16 at 09:11
  • 1
    I had a filter for **Not Like**, and I was attempted to add a filter for **Like**... but that didn't work. It seems like I can only add filters in one group (**Like** or **Not Like**)... but not for both simultaneously. – Lee Grissom Jan 10 '18 at 07:26
  • 2
    thank you, thank you for the `%` syntax reminder. Google brought me here and though the OP question mentions it, this is a very good answer thanks – Alex from Jitbit Mar 12 '20 at 17:54
5

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.

RLH
  • 15,230
  • 22
  • 98
  • 182
  • 2
    This solved it for me. I was editing the properties of the trace and it was having no effect. Using a template and starting a new trace fixed it and saves me time in the future too. – pholcroft Dec 08 '21 at 08:58
0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Martin
  • 3,396
  • 5
  • 41
  • 67
0

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.

Ronald
  • 16
  • 1
  • 3