Getting Recent Search Terms

Hi,
I dont know if this post has been repeated before. Is there a way to
get all the recent search terms passed through Full text search?
Thanks,
P
no, there is no way. On most search projects I have worked on we
asynchronously scrape the web logs for the search terms.
"PShah" <piyush13@.gmail.com> wrote in message
news:fc5b927d.0407231122.495d5ee@.posting.google.co m...
> Hi,
> I dont know if this post has been repeated before. Is there a way to
> get all the recent search terms passed through Full text search?
> Thanks,
> P
|||Hilary,
Actually, there is a way to do this via SQL Profiler, either interactively
using the SQL Profiler GUI tool or via server-side tracing, for example:
-- Filename: FTS_Trace.sql
-- Modified: 4:30 PM 2/19/2003
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
exec @.rc = sp_trace_create @.TraceID output, 0, N'D:\FTS_Activity_Trace',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @.TraceID, 10, 1, 6, N'CONTAINS'
exec sp_trace_setfilter @.TraceID, 10, 1, 6, N'contains'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
PShah, you can also setup a query log table and when you accept the user's
input and then pass it to the Contains* or Freetext* query, you can log the
input to a Query log table for further review. If you provide more details
on what you are looking for, I can provide more specific solutions.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uaqRqiRcEHA.3012@.tk2msftngp13.phx.gbl...
> no, there is no way. On most search projects I have worked on we
> asynchronously scrape the web logs for the search terms.
> "PShah" <piyush13@.gmail.com> wrote in message
> news:fc5b927d.0407231122.495d5ee@.posting.google.co m...
>
|||yes this is a possibility, but in my tests running profiler in a production
environment causes a 10 to 30% performance hit which will exacerbate locking
to a point where it really is unusable.
Your experience may vary, but my experience is that it is unacceptable.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23oVoFIUcEHA.644@.tk2msftngp13.phx.gbl...
> Hilary,
> Actually, there is a way to do this via SQL Profiler, either interactively
> using the SQL Profiler GUI tool or via server-side tracing, for example:
> -- Filename: FTS_Trace.sql
> -- Modified: 4:30 PM 2/19/2003
> declare @.rc int
> declare @.TraceID int
> declare @.maxfilesize bigint
> set @.maxfilesize = 5
> exec @.rc = sp_trace_create @.TraceID output, 0, N'D:\FTS_Activity_Trace',
> @.maxfilesize, NULL
> if (@.rc != 0) goto error
> -- Set the events
> declare @.on bit
> set @.on = 1
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> exec sp_trace_setfilter @.TraceID, 10, 1, 6, N'CONTAINS'
> exec sp_trace_setfilter @.TraceID, 10, 1, 6, N'contains'
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> -- display trace id for future references
> select TraceID=@.TraceID
> goto finish
> error:
> select ErrorCode=@.rc
> finish:
> go
>
> PShah, you can also setup a query log table and when you accept the user's
> input and then pass it to the Contains* or Freetext* query, you can log
the
> input to a Query log table for further review. If you provide more details
> on what you are looking for, I can provide more specific solutions.
> Regards,
> John
>
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:uaqRqiRcEHA.3012@.tk2msftngp13.phx.gbl...
>
|||Hilary,
I only said that this can be done using SQL Profiler vs. "there is no way"
to do this... I didn't say that I would recommend this method over using the
query log approach or visa versa, nor did I say that I would recommend using
the SQL Profiler GUI in production environments. In fact, I agree that using
the SQL Profiler GUI tool to monitor production environment can adversely
affect production performance and that is why I provided the server-side
script. This script or any other profiler server-side script can be executed
is such a way (say, only for 4 minutes periods, executed randomly over a
specified time period, approx. 1 hr.) that will have minimum impact on a
production SQL Server environment.
Actually, I've run SQL Profiler server-side traces at very large customer
(>1TB databases) sites as well as at smaller customer sites with minimal
impact. Additionally, the current SQL Server Magazine has a very good
article entitled "9 Steps to an Automated Trace" at
http://www.winnetmag.com/SQLServer/A...014/43014.html that
almost exactly describes what I've done at large customer sites and I would
recommend using scheduled server-side Profiler traces to get "all the recent
search terms passed through Full text search" over using the SQL Profiler
GUI for large/active production sites.
Finally, I did ask PShaw to provide more details on what he is looking for,
as there are viable solutions to this issue, and if we had more info on what
he is specifically looking for we could provide more and better solutions.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OQze#UYcEHA.2408@.tk2msftngp13.phx.gbl...
> yes this is a possibility, but in my tests running profiler in a
production
> environment causes a 10 to 30% performance hit which will exacerbate
locking[vbcol=seagreen]
> to a point where it really is unusable.
> Your experience may vary, but my experience is that it is unacceptable.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23oVoFIUcEHA.644@.tk2msftngp13.phx.gbl...
interactively[vbcol=seagreen]
user's[vbcol=seagreen]
> the
details
>
|||Strange, I can't get your trace to work. By chance are you filtering on an
application with a name like contains or CONTAINS? ;)
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u1D1KqYcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hilary,
> I only said that this can be done using SQL Profiler vs. "there is no way"
> to do this... I didn't say that I would recommend this method over using
the
> query log approach or visa versa, nor did I say that I would recommend
using
> the SQL Profiler GUI in production environments. In fact, I agree that
using
> the SQL Profiler GUI tool to monitor production environment can adversely
> affect production performance and that is why I provided the server-side
> script. This script or any other profiler server-side script can be
executed
> is such a way (say, only for 4 minutes periods, executed randomly over a
> specified time period, approx. 1 hr.) that will have minimum impact on a
> production SQL Server environment.
> Actually, I've run SQL Profiler server-side traces at very large customer
> (>1TB databases) sites as well as at smaller customer sites with minimal
> impact. Additionally, the current SQL Server Magazine has a very good
> article entitled "9 Steps to an Automated Trace" at
> http://www.winnetmag.com/SQLServer/A...014/43014.html that
> almost exactly describes what I've done at large customer sites and I
would
> recommend using scheduled server-side Profiler traces to get "all the
recent
> search terms passed through Full text search" over using the SQL Profiler
> GUI for large/active production sites.
> Finally, I did ask PShaw to provide more details on what he is looking
for,
> as there are viable solutions to this issue, and if we had more info on
what[vbcol=seagreen]
> he is specifically looking for we could provide more and better solutions.
> Regards,
> John
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OQze#UYcEHA.2408@.tk2msftngp13.phx.gbl...
> production
> locking
> interactively
example:[vbcol=seagreen]
N'D:\FTS_Activity_Trace',[vbcol=seagreen]
> user's
log[vbcol=seagreen]
> details
to
>
|||Hilary,
Sorry, that code was one of many 'test' versions that I had created during
my testing of this issue last year... I've attached a zip file that contains
two sql files, the Trace start & stop .sql files and the .trc output file
that demonstrates exactly how to do this.
It's still a bit crude and I would not recommend running - this version - on
production servers, as it is a proof-of-concept that server-side Profiler
tracing of FTS queries is not only possible, but functional. With
modifications and via careful scheduling of when and how often the
server-side trace is executed, it can be used in production environments
without adversely affecting the overall SQL Server performance... Below is
the primary difference:
EXEC @.RC = sp_trace_setfilter @.TraceID, 1, 1, 6, N'%CONTAINS%'
EXEC @.RC = sp_trace_setfilter @.TraceID, 1, 1, 6, N'%contains%'
EXEC @.RC = sp_trace_setfilter @.TraceID, 1, 1, 6, N'%containstable%'
EXEC @.RC = sp_trace_setfilter @.TraceID, 1, 1, 6, N'%freetext%'
EXEC @.RC = sp_trace_setfilter @.TraceID, 1, 1, 6, N'%freetexttable%'
PShaw, if you reply with more detailed requirements of your environment and
how you would like to get the recent search terms that are passed through to
Full text search, I can also provide solutions that use FTS query logging
techniques.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O85RLKZcEHA.2812@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Strange, I can't get your trace to work. By chance are you filtering on an
> application with a name like contains or CONTAINS? ;)
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:u1D1KqYcEHA.1000@.TK2MSFTNGP12.phx.gbl...
way"[vbcol=seagreen]
> the
> using
> using
adversely[vbcol=seagreen]
> executed
customer[vbcol=seagreen]
that[vbcol=seagreen]
> would
> recent
Profiler[vbcol=seagreen]
> for,
> what
solutions.[vbcol=seagreen]
unacceptable.[vbcol=seagreen]
> example:
> N'D:\FTS_Activity_Trace',
> log
way
> to
>
begin 666 FTS_Profiler_Trace_files.zip
M4$L#!!0````(`.-.^# XV'5NAP(``*,&```<````1E137U!R;V9I;&5R7U1R
M86-E7W-T87)T+G-Q;*54RV[;,! \5X#^82^!$D.6'S4:U$#0N(H#I$WMUE:!
M'@.H8-+6.5,BD0E))/[]<2K85- 'RH'T0I=V9V>%R?:_;?;?)"Q1LBV.X3):K
M[TK2"[5*%..XTH8I$^G;PJ?0LE*EU#8RD9!*7FU1&,CD/1@.)E498_KB&'0!H
M5'>HNCI/$0R!411G964JA<0%L12&Y4)W@.(D4+A6BP;^F`[<5JARUH]S*--_D
MF([AXWAT"I-O<-H;CGK#?G_DOD\JDTDUAB\R$Y!$\)4)]#WZD:"R6EN8&^E[
M%]/X>K*8PODBAER8$,Y=@.5<7S6X^@.W5N?&\Y3=SF# :^-_TUC5W&&>ARY8I8
M<87,X"%=5L:6%,(PA%G Q[__-S$@.U.MIG$ `BSBP8!8RA*!!",YV6*2ZVX6E
ML03.6BZW96%-20'O[%Z',%__06YF]KCLQZ+:"@.#*F7->*0WW&0HP&8(%%)IQ
MTZ43T7N\C.D6IK2&6;\OF&&/EZIM&/'NBPUA- AAX.QJQ'YFAF?/$NK"VT+9
M0YEKA_1 8E0G.12Y`>DP@.7&.6MNOSU8]L(?S?M3(?HEH%TTFP1TK*H042Q0I
M@.<O:Z)J)%TQKNV<&<DT]3BV>VLYR,4[6R\2V+5ZB`=M-QEZIC52-L $<[\X.
M/IV$D-(#K^]3+F[<]+53>>*^,)^@.:ZQ=]W_!^HG]L7.@.`+DD<8A8V<0,/\
M9W*X\+6+4?1TJ8]RV?_IHUQ2W>2"%;7U^@.6@.!_U'\7R63*YFRZ,`7@.?0>*J/
M@.K?E&[8N\+4@.FV8TOC5_+V(W9Y1I1O,Q#8E*PX!@.Z?W)DTQ-9(N)9E"O0T>V
M0-OY0H]]SZ;MUFY2TB+B_8+VL^_UX;#L\.WT2.<_4$L#!!0````(`)-.^# +
M:4OW? 8``````@.`6````1E137U!R;V9I;&5R7U1R86-E+G1R8^W::T\<51C
M\0=:2ZF8](4QC2]TTJ19VE N+19MTMA[THN@.@.%&CIEE8**O0I3M0_1Z^]6/X
M`7SI!_"-W\,8KUF?YSESF NSIM06-/Q_DUUV9^:<\YS+S)Z9X==!.?Z]B!R1
M_Z?7);DKBW)//I([,BL79%*7B_*9+,@.'<E^_WY.?>X,C\E?OS8%!W?_(2UG"
MH^M#`V)+]&?OM?,R(H,G=9M^/>N?S!^][TZ(#)W*LDSD?>E*1U:E+>NRHI\U
MCV.ORAFY(7,:PJ)<\U 6=(WF[UN6=?]'LB5-3...(TFS+&S5;[-.2YYNG7M4R
M5G39TM?7^IZGKFXIIOZ]=_+'H2$Y)J=D6-OAN'PS\$OOJ%904_>Z+XO<E%L:
M\GT-=UX_)7)5_][0OVT/:,S7+&H!30UR1:MTL[+-*IMH<6U=\XJ<T K?TOWS
M+5?T->5;;LG'7E9>AFU+95,>:-I8P@.-]M^HTO3IUI7=D6[=M^KO%<,'?9Z6A
M^US6/K^M*18TGVNZ1\OC>J!K5KQ9+?*'NF>,]+Y'9/$V]&51-;*X0HQCOJ4<
M04.W5Z.R_.QU7I=$<][*XM_0E[55XEV\H5&O9QW5TG4K\B3;GGI)<]J.7^B:
M95TSJSEL> M8RG6MRX;NF>@.2RYG3+<NZOJNI$_E*UCRO1-.N>;H0H0VGIN=X
M?F?HIC7QK>F:M$^<'<]W,1M>-SUM<T]]FF:Y/=DIK]J"5O]I'2GV=ZHPMLHM
M>]W+7M9HGU^+YKGW:]'F/[;F4B&F_JTX7BHIC\5.(?:>QYEX"=8NJ;>;I7W^
M;3V5'3<7M=7+K?VB6CK/.XZD1/-H^G;;MZ7OFYYG:R?R3FE$%^NTK.F:WD)A
M>]/7MK/6;VI.6]Z3(=IV*9^\M5YDR_8;Q0N>2Y+]=%A^7?_6\;_%%IO2[Z.[
MCKM$WM7?I3%OL;BF_ ,2SG))=B9,...Q:*Q3/>'EO[*WVY:CKZS]9J/^EG;-S
MOQ]..^.&2-K:']8G8Y76:7BOSLF'?J:NIB^/Q7%?]MZK3U^O\#ZSAWI9SS[T
M7K'17ASUZ0N*M*[]ZZ8F%N-^1K!["M0XT/*+4Z7]C63W=.Y@.R]_=$M7Y3%?L
MK)7'8.>F.)/8]B-T:B?:N/_99ZA3.<_Z.L5YT(2<DWB4S7MJ.^=;SU[V[:&T
MZE*=4<8EUGCWDO1=;VDF:\I(),Y[SVF4L3WM-2)'+^H%A4[#]>I"I^&K5FUK
M^/#C;Q5:]5/)AGZR:>Z2-HT-5_N)B-.2KL2?U^)P'M74UD -3=/1Y4M=U\@.Z
M08M]*Q0[<C#%AMJVGKW8ZNDK+S8.XGYUW>="0TW7GKW0ZL&9%VK3SS @.5*Q1\
MJ5C;`RCX7_?M;;_\M$M'.UDL%@.J.DYQ^M=WG0D-->]\.2.4"TB9;=J%Y7=>-
M:])M_;;N/_DV%1[KL\=J:8][FMLGA7WF==C-ZEH+(<D"GI/W)/'4-IVUJ47J
MW]-*"8D/V%G-<5X_W]5T=[*)Z&@.E\G.EG&-98:D.?_MTW=..5F(8JZWS59_N
MVH^!74HL^.G^K"Z)YK.PJ\:A].'LUD%=>[5](GNEIJT^S=9\7CM*>N.#XM<5
MX;HA]']=<&%V%M;8WU%YV^\F368C(;^_49?VBH^>TSK;"\?1(_T<YG^)? K*Q
MU_9CZ[34W8-@."/TWA] A.^;[3Y(YYCGF#\D0.F3'?/_+08YYCOE#,H0.V3%?
MO?QA;L\Q?UB'T \#XC?TAB7>P(]A7LZ*LD?*U5O\EGWQ]N66;MW4PE)YK'M8
M02%/NPW1U?>.I%F.BWXSHJ7O]L"Q^!BVHT,I\9P2V?;][09L_2.&Q+=W_?%'
M5TM*O0'*C[AB7N&AF'7)ML3;'^'QT(W*9,TZ/#XRNETYJ=NVQ[+MY;4E/L (
MM=S0?.R!^ZIO:7D]W]'W:9GQ#K7A,R,3<D'73&3_I#%=2'^M,& L[5W]M";A
MT?.X=ZE%M>(IXBNV4+C1D\;.G='._:WXKP_3<D9^$@.``````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````````````````````.R/OP%02P,$% ````@.`*$[X
M,/C);G,X`0``% (``!L```!&5%-?4')O9FEL97)?5')A8V5?<W1O<"YS<6R-
MD4]/PS ,Q<]4ZG?P>6+=F"9-5)I$U742,!"L/7"K2NO22&W2Q0[P\4E2_ATX
MH%PBY]F_YY<PF,_/6M&CK :,85_DY8-6KJ#+0E<UEL1JC.C4ATXY&CTJLL)"
M0:-J,Z!DZ-0;L )#"/GC`;[Z@.5"_HIZ3:!#8S7*JNAH-&XT.!:F27 E),ZAD
M`WN-R/C.,S@.9U +)(P?5B%9@.$\-EO-Y <@.>;Q6J]6"V7:_^>&.Z4CN%&=1**
M"&XKB6'@.CC,TFF<[YD4Y*;A5@.+N)[1V%P2Y+#\DQ@.ZMC"D+RK X)?_WHW5?.L
M^*ELX2(,LJ<L]5U;H+'D*2MDXHH-?6O/80F6G%LR>32-6/M])@..1-Y;<[Z#N
M;;!>\NGLOX"5`Z2N^T^"C[;!WD9+()CLO152L% 26JV&J<=_533E]@.%02P$"
M% `4````" #C3O@.P.-AU;H<"``"C!@.``' `````````!`" `MH$`````1E13
M7U!R;V9I;&5R7U1R86-E7W-T87)T+G-Q;%!+`0(4`!0````(`)-.^# +:4OW
M? 8``````@.`6````````````( "V@.<$"``!&5%-?4')O9FEL97)?5')A8V4N
M=')C4$L!`A0`% ````@.`*$[X,/C);G,X`0``% (``!L``````````0`@.`+:!
M<0D``$944U]0<F]F:6QE<E]4<F%C95]S=&]P+G-Q;%!+!08``````P`#`-<`
(``#B"@.``````
`
end