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''??

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''''%'
|improve this answer|||||
  • it is pretty obvious but for some reason I was trying this: and textdata not like '%@ListNoteTypeCode=N'''',%' – Marcello Miorelli Aug 12 '16 at 17:39
  • 1
    @marcellomiorelli that looks like it should work too from the screenshot. Maybe there is some white space character between the end quote and the comma that is not apparent in that. – Martin Smith Aug 12 '16 at 17:44
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.

|improve this answer|||||
  • bulletPoint is only valid on specific cases, there are a variety of things that could be there. the picture is only a partial representation, valid only to one of our different markets... – Marcello Miorelli Aug 12 '16 at 17:36
  • 1
    I knew I was reaching on that one, but I was trying to keep it in the same statement. Thanks for the clarification. – SS_DBA Aug 12 '16 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.