0

I have been running a server side trace in order to find out how a specific stored procedure is called within my database.

I am running the following query in order to get data from the trace file generated:

select TextData,
db_name(databaseid)
from fn_trace_gettable( 'R:\Profiler\udpProductTaxRateGet4.trc', default) fn
join sys.trace_events te
 on fn.EventClass = te.trace_event_id
where --
1=1
--and EventClass = 42
and textdata like '%udpProductBulletPointSelectByTier1NoteTypeCode%'
--and db_name(databaseid) = 'US16AUTPProduct'
order by starttime desc

But as you can see on the picture below, there are too many rows like this one:

declare @p5 int  set @p5=86400 exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode @Tier1=N'AR763',@LanguageID=2,@SeasonItemID=N'16AUT',@ListNoteTypeCode=N'',@CacheDuration=@p5 output  select @p5

enter image description here

more specifically the procedure was called with an empty parameter value for @ListNoveTypeCode

@ListNoteTypeCode=N''

How can I filter out all those rows where @ListNoteTypeCode=N''??

Community
  • 1
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

2 Answers2

4

The single quote character has no special significance to LIKE

You need to double them up to escape them inside any string literal.

So the two consecutive single quotes in the string you are trying not to match become four as below.

And textdata not like '%@ListNoteTypeCode=N''''%'
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
1

From the image, it looks like the BulletPoint is what you need to look for as well. Change the Like statement to like '%udpProductBulletPointSelectByTier1NoteTypeCode%BulletPoint%'

This should reduce the results.

SS_DBA
  • 453
  • 2
  • 7