"Paging" in SP

I need to searches through records using Full Text Indexing. A search often
returns hundreds of records, and need the user to be able to page through
the result 50 at a time. I *ALSO* need the user to know the first and last
record of each page! (..On the client, i render a list of all pages in the
resultset, and add labels to each page, indicating to the user where in the
resultset the page is positioned).
I have therefor done an SP that inserts my initial search into an in-memory
table. After that i loop through all the records from my initial search, and
insert every 50th record into a different in-memory table. That allows me to
return a resultset with a) the first page of records (first 50 records), and
a table with a complete list of pages.
However, it has come to my attention that my loop through my initial
resultset is taking a lot of performance. So basically, my question is:
i have a large resultset in memory. I need to "sample" records from this,
eg. every 50th record. What would be the fastest way of doing this?
Cheers,
/Thomas.
You are using the wrong approach for this.
Here is some sample code that uses asp and getrows.
Approaches like the one you are using do not offer the best performance.
Check out
http://www.adopenstatic.com/experime...dsetpaging.asp
for more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> I need to searches through records using Full Text Indexing. A search
often
> returns hundreds of records, and need the user to be able to page through
> the result 50 at a time. I *ALSO* need the user to know the first and last
> record of each page! (..On the client, i render a list of all pages in the
> resultset, and add labels to each page, indicating to the user where in
the
> resultset the page is positioned).
> I have therefor done an SP that inserts my initial search into an
in-memory
> table. After that i loop through all the records from my initial search,
and
> insert every 50th record into a different in-memory table. That allows me
to
> return a resultset with a) the first page of records (first 50 records),
and
> a table with a complete list of pages.
> However, it has come to my attention that my loop through my initial
> resultset is taking a lot of performance. So basically, my question is:
> i have a large resultset in memory. I need to "sample" records from this,
> eg. every 50th record. What would be the fastest way of doing this?
> Cheers,
> /Thomas.
>
begin 666 citrixNew.asp
M/"4-"F]P=&EO;B!E>'!L:6-I= T*0V]N<W0@.8615<V5#;&EE;G0@./2 S#0I#
M;VYS="!A9%5S95-E<G9E<B ](#(-"D-O;G-T(&%D3W!E;D9O<G=A<F1/;FQY
M(#T@., T*0V]N<W0@.861/<&5N4W1A=&EC(#T@.,PT*0V]N<W0@.861,;V-K4F5A
M9$]N;'D@./2 Q#0I#;VYS="!A9$-M9%-T;W)E9%!R;V,@./2 T#0I#;VYS="!A
M9%!A<F%M26YP=70@./2 Q#0I#;VYS="!A9%!A<F%M4F5T=7)N5F%L=64@./2 F
M2# P,#0-"D-O;G-T(&%D5F%R0VAA<B ](#(P, T*0V]N<W0@.861);G1E9V5R
M(#T@.,PT*0V]N<W0@.:5)O=W-097)086=E(#T@.,3 -"D-O;G-T('-H;W=";&%N
M:R ]("(F;F)S<#LB#0I#;VYS="!S:&]W3G5L;" ](").54Q,(@.T*#0I$:6T@.
M:5-T87)T+"!2971U<FY686QU92P@.5V5B2&ET<U%U97)Y+"!S=')39 6%R8V@.-
M"@.T*36%I;@.T*#0I3=6(@.36%I;B@.I#0H)1&EM(')S="P@.<U -13"P@.<T-O;FY3
M=')I;F<L<U-C<FEP=$YA;64L(%-E87)C:%]0:')A<V4L(%-E87)C:%]0:')A
M<V5?5')I;6UE9 T*"6E3=&%R=#U297%U97-T+E%U97)Y4W1R:6YG*")I4W1A
M<G0B*0T*"5-E87)C:%]0:')A<V4]4F5Q=65S="Y1=65R>5-T<FEN9R@.B4V5A
M<F-H7U!H<F%S92(I#0H))U-E87)C:%]0:')A<V5?5')I;6UE9#U297!L86-E
M*%-E87)C:%]0:')A<V4L(&-H<B@.S.2DL(&-H<B@.S.2DK8VAR*#,Y*2D-"@.DG
M4V5A<F-H7U!H<F%S95]4<FEM;65D/5)E<&QA8V4H4V5A<F-H7U!H<F%S95]4
M<FEM;65D+"!C:'(H,S0I+"!C:'(H,S0I*V-H<B@.S-"DI#0H):68@.:5-T87)T
M/2(B('1H96X-"@.D):5-T87)T/3 -"@.EE;F0@.:68-"@.T*"7-#;VYN4W1R:6YG
M(#T@.(E!R;W9I9&5R/5-13$],141"+C$[4&5R<VES="!396-U<FET>2!);F9O
M/49A;'-E.T1A=&$@.4V]U<F-E/2X[=7-E<B!)1#US83MP=V0]<V4Q8W5R92,[
M26YI=&EA;"!#871A;&]G/71E>'0[(@.T*#0H)5W)I=&5486)L94AE861E<B!S
M4V-R:7!T3F%M92P@.4V5A<F-H7U!H<F%S90T*#0H)<U-C<FEP=$YA;64@./2!2
M97%U97-T+E-E<G9E<E9A<FEA8FQE<R@.B4T-225!47TY!344B*0T*"6EF(%-E
M87)C:%]0:')A<V4@./#XB(B!T:&5N#0H)"5=R:71E5&%B;&5";V1Y(&E2;W=S
M4&5R4&%G92P@.:5-T87)T+"!S4V-R:7!T3F%M92P@.<T-O;FY3=')I;F<L('-3
M44PL(%-E87)C:%]0:')A<V4-"@.EE;F0@.:68-"@.E7<FET951A8FQE1F]O=&5R
M#0H-"D5N9"!3=6(-"@.T*4W5B(%=R:71E5&%B;&5(96%D97(H<U-C<FEP=$YA
M;64L4V5A<F-H7U!H<F%S92 I#0H)<F5S<&]N<V4N=W)I=&4@.(CQ(5$U,/B(-
M"@.ER97-P;VYS92YW<FET92 B/$A%040^(@.T*"7)E<W!O;G-E+G=R:71E("(\
M345402!H='1P+65Q=6EV/2(B0V]N=&5N="U4>7!E(B(@.8V]N=&5N=#TB(G1E
M>'0O:'1M;#L@.8VAA<G-E=#UW:6YD;W=S+3$R-3(B(CXB#0H)<F5S<&]N<V4N
M=W)I=&4@.(CQ,24Y+(&AR968](B(O25,O<W1Y;&4N8W-S(B(@.='EP93TB(G1E
M>'0O8W-S(B(@.<F5L/2(B<W1Y;&5S:&5E="(B/B(-"@.ER97-P;VYS92YW<FET
M92 B/"](14%$/B(-"@.ER97-P;VYS92YW<FET92 B/$)/1%D@.=DQI;FL](B(C
M,# P,# P(B(@.;&EN:STB(B,P,# P,# B(B!B9T-O;&]R/2(B(V9F9F9F9B(B
M(&QE9G1-87)G:6X](B(P(B(@.=&]P36%R9VEN/2(B,"(B(&UA<F=I;FAE:6=H
M=#TB(C B(B!M87)G:6YW:61T:#TB(C B(CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQF;W)M(&ED/2(B9F]R;3$B(B!M971H;V0](B)G970B(B!A8W1I;VX](B(B
M("8@.<U-C<FEP=$YA;64@.("8@.(B(B/B(-"@.ER97-P;VYS92YW<FET92 B/'1A
M8FQE(&)O<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C$P,"4B(B!C
M96QL4W!A8VEN9STB(C B(B!C96QL4&%D9&EN9STB(C4B(B!W:61T:#TB(C$P
M,"4B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B(&)O<F1E<CTB(C B(CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T
M:#TB(C$P,"(B(&)G0V]L;W(](B(C8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF
M;F)S<#L\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D(&-L87-S/2(B;F5W
M+71D+79E<G1I8V%L(B(@.=VED=&@.](B(U(B(@.8F=#;VQO<CTB(B-A-V)B.#8B
M(B!H96EG:'0](B(W,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H
M:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CQT9"!W:61T:#TB(C(P(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#LB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L
M:6=N/2(B=&]P(B(@.=VED=&@.](B(U,# B(B!H96EG:'0@./2 B(C@.P(B(^/$E-
M1R!H96EG:'0](B(V,"(B(&AS<&%C93TB(C(P(B(@.<W)C/2(B25,O:7-F7VQO
M9V\N9VEF(B(@.('=I9'1H/2(B,34R(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I
M=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B(&)G0V]L;W(](B(C
M8V)E-6$V(B(@.:&5I9VAT/2(B-S B(CXF;F)S<#L\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I9'1H /2(B,3 P
M(B(@.8F=#;VQO<CTB(B,U8CDS864B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I
M9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T
M9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C,V4W-CDQ(B(@.:&5I9VAT/2(B,3 B(CX\
M24U'(&AE:6=H=#TB(C$B(B!S<F,](B))4R]S:&EM+F=I9B(B('=I9'1H/2(B
M,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,](B)N97<M
M=&0B(B!W:61T:#TB(C(P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B( B!H96EG:'0]
M(B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.
M=VED=&@.](B(Q(B(^/"]T9#XB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S
M<STB(FYE=RUT9"(B('9!;&EG;CTB(F-E;G1E<B(B('=I9'1H/2(B-3 P(B(@.
M8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q(B(^/'-T<F]N9R!C;&%S
M<STB(G1E>'0B(B!S='EL93TB(DU!4D=)3BU,1494.B R,'!X(B(^4V5A<F-H
M.CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.]
M(B(Q,"(B/B9N8G-P.R9N8G-P.R(-"@.ER97-P;VYS92YW<FET92 B/$E.4%54
M(&ED/2(B4V5A<F-H7U!H<F%S92(B('1Y<&4](B)T97AT(B(@.<VEZ93TB(C,P
M(B(@.;F%M93TB(E-E87)C:%]0:')A<V4B(B!V86QU93TB(B(@.)B!396%R8VA?
M4&AR87-E("8@.(B(B('-T>6QE/2(B34%21TE.+51/4#H@.+31P>#L@.34%21TE.
M+4)/5%1/33H@.+3-P>"(B('9A;'5E/2(B(B(^(#QA(&ED/2(B8G1H1V\B(B!C
M;&%S<STB(F=O(B(@.:')E9CTB(FIA=F%S8W)I<'0Z9F]R;3$N<W5B;6ET*"D[
M(B(^(@.T*"7)E<W!O;G-E+G=R:71E(")';R$\+V$^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C4B(CXF;F)S<#L@./"]S
M=')O;F<^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1D/B(-"@.ER97-P;VYS92YW
M<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B)V06QI9VX](B)T;W B(B!B9T-O
M;&]R/2(B(S5B.3-A92(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q
M(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER
M97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.(CQT<CXB
M#0H)<F5S<&]N<V4N=W)I=&4@.(CQT9"!W:61T:#TB(C$P,"(B(&)G0V]L;W(]
M(B(C8V)E-6$V(B(@.:&5I9VAT/2(B,3 P(B(^)FYB<W [/"]T9#XB#0H)<F5S
M<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"UV97)T:6-A;"(B('=I
M9'1H/2(B-2(B(&)G0V]L;W(](B(C83=B8C@.V(B(@.:&5I9VAT/2(B,3 P(B(^
M/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB
M(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B/'1D('9!;&EG;CTB(G1O
M<"(B('=I9'1H/2(B,C B(B!B9T-O;&]R/2(B(V8Y9F5E9"(B/B9N8G-P.SPO
M=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED
M=&@.](B(U,# B(B!H96EG:'0@./2 B(C$P,"4B(CXB#0H-"D5N9"!3=6(-"@.T*
M4W5B(%=R:71E5&%B;&5";V1Y*&E2;W=S4&5R4&%G92P@.:5-T87)T+'-38W)I
M<'1.86UE+"!S0V]N;E-T<FEN9RP@.<U-13"Q396%R8VA?4&AR87-E*0T*"41I
M;2!I3&]O<"P@.86QL9&%T82P@.;G5M8V]L<RP@.;G5M<F]W<RP@.:5-T;W L(&E2
M;W=,;V]P+"!C;VQC;W5N=&5R+"!T:&ES9FEE;&0-"@.E$:6T@.8VYN+"!R<W0L
M(&-M9"P@.<&%R86TL('!A<F%M,2P@.<&%R86TR#0H-"@.ES970@.8VYN(#T@.4V5R
M=F5R+D-R96%T94]B:F5C="@.B041/1$(N0T].3D5#5$E/3B(I#0H)8VYN+D-O
M;FYE8W1I;VY3=')I;F<@./2!S0V]N;E-T<FEN9PT*"6-N;BY/<&5N#0H-"@.ES
M970@.8VUD/5-E<G9E<BY#<F5A=&5/8FIE8W0H(D%$3T1"+D-O;6UA;F0B*0T*
M#0H)8VUD+D-O;6UA;F1497AT/2)Q=65R>2(-"@.EC;60N0V]M;6%N9%1Y<&4]
M861#;613=&]R9610<F]C#0H)8VUD+D%C=&EV94-O;FYE8W1I;VX]8VYN#0H-
M"@.E3970@.<&%R86T@./2!C;60N0W)E871E4&%R86UE=&5R*") <W1R4V5A<F-H
M(BP@.861687)#:&%R("P@.861087)A;4EN<'5T+" R,# L4V5A<F-H7U!H<F%S
M92D-"@.EC;60N4&%R86UE=&5R<RY!<'!E;F0@.<&%R86T-"@.T*"7-E="!R<W0@.
M/2!C;60N97AE8W5T90T*#0H):4QO;W @./2 Q#0H):68@.<G-T+D5/1CU&04Q3
M12!T:&5N#0H)"6%L;&1A=&$]<G-T+D=E=%)O=W,-"@.D);G5M8V]L<SUU8F]U
M;F0H86QL9&%T82PQ*0T*"0EN=6U2;W=S/75B;W5N9"AA;&QD871A+#(I*S$-
M"@.EE;'-E( T*"0EN=6U2;W=S/3 -"@.EE;F0@.:68@.#0H-"@.T*"7)S="YC;&]S
M90T*"6EF(&YU;5)O=W,\/B P('1H96X-"@.T*"0EI9B!N=6U2;W=S/BAI4F]W
M<U!E<E!A9V4@.*VE3=&%R="D@.=&AE;@.T*"0D):5-T;W ]:5)O=W-097)086=E
M("MI4W1A<G0M,0T*"0EE;'-E#0H)"0EI4W1O<#UN=6U2;W=S("TQ#0H)"65N
M9"!I9@.T*#0H)"7)E<W!O;G-E+G=R:71E(&YU;5)O=W,@.)B B(')E8V]R9',@.
M9F]U;F0@.+2!$:7-P;&%Y:6YG("(@.)B!I4W1A<G0@.*S$@.)B B('1H<F]U9V@.@.
M(B F(&E3=&]P("LQ("8@.(CQ"4CXB#0H-"@.D)268@.:5-T87)T(#X@.,"!4:&5N
M#0H)"0E297-P;VYS92Y7<FET92 B(#QA(&AR968](B F('-38W)I<'1.86UE
M("8@.(C]396%R8VA?4&AR87-E/2(F4V5A<F-H7U!H<F%S92 F("(F:5-T87)T
M/2(F*&E3=&%R="UI4F]W<U!E<E!A9V4I("8@.(CX\/"!0<F5V:6]U<SPO83XB
M#0H)"45N9"!)9@.T*#0H)"4EF(&E3=&%R=" K(&E2;W=S4&5R4&%G92 \(&YU
M;5)O=W,@.5&AE;@.T*"0D)4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)B H:5-T87)T*VE2;W=S4&5R4&%G92D@.)B B/DYE>'0^
M/CPO83XB#0H)"45N9"!)9@.T*"0ER97-P;VYS92YW<FET92 B/%1A8FQE(&)O
M<F1E<D-O;&]R/2(B(V$W8F(X-B(B(&AE:6=H=#TB(C4E(B(@.8V5L;%-P86-I
M;F<](B(P(B(@.8V5L;%!A9&1I;F<](B(U(B(@.=VED=&@.](B(Q,# E(B(@.8F=#
M;VQO<CTB(B-F.69E960B(B!B;W)D97(](B(Q(B(^(@.T*"0ER97-P;VYS92YW
M<FET92 B/%12/B(-"@.D)<F5S<&]N<V4N5W)I=&4@.(CQ42#Y(:6=H;&EG:'1I
M;F<\+U1(/CQ42#Y296-O<F0@.3G5M8F5R/"]42#X\5$@.^0VAA<F%C=&5R:7IA
M=&EO;CPO5$@.^/%1(/D-R96%T92!$871E/"]42#X\5$@.^4VEZ93PO5$@.^/%1(
M/E)A;FL\+U1(/B(-"@.D)<F5S<&]N<V4N=W)I=&4@.(CPO5%(^(@.T*#0H)"49/
M4B!I4F]W3&]O<#T@.:5-T87)T('1O(&E3=&]P#0H)"0ER97-P;VYS92YW<FET
M92 B/%12/B(@.)B!V8F-R;&8-"@.T*"0D)1D]2(&-O;&-O=6YT97(],"!T;R!N
M=6UC;VQS#0H)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.(CQT9"!V86QI9VX]
M=&]P/B(-"@.D)(" @.(" @.("!I9B )8V]L8V]U;G1E<CTP('1H96X-"@.D)(" @.
M(" @.(" @.5V5B2&ET<U%U97)Y(#T@.(B9#:5=E8DAI='-&:6QE/2(@.)B!397)V
M97(N55),16YC;V1E*')T<FEM*&%L;&1A=&$H8V]L8V]U;G1E<BLQ+&E2;W=,
M;V]P*2DI("8B)D-I4F5S=')I8W1I;VX](B F(%-E<G9E<BY54DQ%;F-O9&4H
M4V5A<F-H7U!H<F%S92D-"@.D)(" @.(" @.(" @.4F5S<&]N<V4N5W)I=&4@.(CQ0
M/CQA(&AR968]+V]O<"]Q:&ET+FAT=S]#:4AI;&ET951Y<&4]4W5M;6%R>2(@.
M)B!796)(:71S475E<GD@.)B B/CQ)34<@.<W)C/2(@.)B(O:&EL:6=H="YG:68B
M("8@.(B!A;&EG;CUL969T(&%L=#TB("8@.(DAI9VAL:6=H="!M8 71C:&EN9R!T
M97)M<R!I;B!D;V-U;65N="!U<VEN9R!3=6UM87)Y(&UO9&4N(B F("(^/% ^
M4W5M;6%R>3PO83X\4#XB#0H@.( D)"0D)"5)E<W!O;G-E+E=R:71E("(\0E(^
M/% ^/% ^/&$@.:')E9CTO;V]P+W%H:70N:'1W/T-I2&EL:71E5'EP93U&=6QL
M(B F(%=E8DAI='-1=65R>2 F("(^/$E-1R!S<F,](B8@.("(O:&EL:6=H="YG
M:68B)B(@.86QI9VX];&5F="!A;'0](B8B2&EG:&QI9VAT(&UA=&-H:6YG('1E
M<FUS(&EN(&1O8W5M96YT+B(F(CX\4#Y&=6QL/"]A/CQ0/B(-"@.D)(" @.(" @.
M("!E;'-E:68@."6-O;&-O=6YT97(],2!T:&5N#0H)"2 @.(" @.(" @."7)E<W!O
M;G-E+G=R:71E("(\82!H<F5F/2(B+V5D:70N87-P/U!+/2(F86QL9&%T82AC
M;VQC;W5N=&5R+3$L:5)O=TQO;W I)B(B(CXB("8@.(&E2;W=,;V]P*S$@.)B B
M/"]A/B(-"@.D)(" @.(" @.("!E;'-E#0H)"0D)(" @.(" @.("!T:&ES9FEE;&0]
M86QL9&%T82AC;VQC;W5N=&5R+&E2;W=,;V]P*0T*"0D)"2 @.(" @.(" @.:68@.
M:7-N=6QL*'1H:7-F:65L9"D@.=&AE;@.T*"0D)"0D)"0D@.('1H:7-F:65L9#US
M:&]W;G5L; T*"0D)"0D)"0EE;F0@.:68-"@.D)"0D)"0D):68@.=')I;2AT:&ES
M9FEE;&0I/2(B('1H96X-"@.D)"0D)(" )"0ET:&ES9FEE;&0]<VAO=V)L86YK
M#0H)"0D)"0D)96YD(&EF#0H-"@.D)"2 @.(" @.(" @.<F5S<&]N<V4N=W)I=&4@.
M=&AI<V9I96QD#0H)"0D@.(" @.96YD(&EF#0H)"2 @.(" @.(" @.<F5S<&]N<V4N
M=W)I=&4@.(CPO=&0^(@.T*"0D@.(" @.($Y%6%0-"@.D)(" @.("!R97-P;VYS92YW
M<FET92 B/"]T<CXB#0H)"4Y%6%0-"@.T*#0H)"7)E<W!O;G-E+G=R:71E("(\
M+W1A8FQE/B(-"@.D);VX@.97)R;W(@.9V]T;R P#0H)"4EF(&E3=&%R=" ^(# @.
M5&AE;@.T*(" @.(" @.(" )4F5S<&]N<V4N5W)I=&4@.(B \82!H<F5F/2(@.)B!S
M4V-R:7!T3F%M92 F("(_4V5A<F-H7U!H<F%S93TB)E-E87)C:%]0:')A<V4@.
M)B B)FE3=&%R=#TB)BAI4W1A<G0M:5)O=W-097)086=E*2 F("(^/#P@.4')E
M=FEO=7,\+V$^(@.T*"0E%;F0@.268-"@.T*"0E)9B!I4W1A<G0@.*R!I4F]W<U!E
M<E!A9V4@./"!N=6U2;W=S(%1H96X-"@.D)"5)E<W!O;G-E+E=R:71E("(@./&$@.
M:')E9CTB("8@.<U-C<FEP=$YA;64@.)B B/U-E87)C:%]0:')A<V4](B9396%R
M8VA?4&AR87-E("8@.(B9I4W1A<G0](B8@.*&E3=&%R="MI4F]W<U!E<E!A9V4I
M("8@.(CY.97AT/CX\+V$^(@.T*"0E%;F0@.268-"@.EE;'-E#0H)"7)E<W!O;G-E
M+G=R:71E(").;R!M871C:&EN9R!R96-O<F1S(&9O=6YD+B(-"@.EE;F0@.:68-
M"@.T*96YD('-U8@.T*#0I3=6(@.5W)I=&5486)L949O;W1E<@.T*#0H)<F5S<&]N
M<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.8VQA<W,]
M(B)N97<M=&0M=F5R=&EC86PB(B!B9T-O;&]R/2(B(V-B935A-B(B(&AE:6=H
M=#TB(C$P,"(B/B9N8G-P.SPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\+W1R
M/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D(B(@.=VED=&@.](B(Q,# B(B!B9T-O;&]R/2(B
M(V$W8F(X-B(B(&AE:6=H=#TB(C$P(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C
M/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D(&-L87-S/2(B;F5W+71D(B(@.8F=#;VQO<CTB(B,S93<V
M.3$B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B,2(B('-R8STB(FEM
M86=E<R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E
M+G=R:71E("(\=&0@.8VQA<W,](B)N97<M=&0B(B!W:61T:#TB(C(P(B(@.8F=#
M;VQO<CTB(B,W.&(P8V(B(B!H96EG:'0](B(Q,"(B/CQ)34<@.:&5I9VAT/2(B
M,2(B('-R8STB(DE3+W-H:6TN9VEF(B(@.=VED=&@.](B(Q(B(^/"]T9#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CQT9"!C;&%S<STB(FYE=RUT9"(B('9!;&EG; CTB
M(G1O<"(B('=I9'1H/2(B-3 P(B(@.8F=#;VQO<CTB(B,W.&(P8V(B(B!H96EG
M:'0](B(Q,"(B/B(-"@.ER97-P;VYS92YW<FET92 B/' @.8VQA<W,](B)P+6QI
M9VAT(B(^*&,I(&-O<'ER:6=H=#QA(&AR968](B)H='1P.B\O=W=W+G-M:6QL
M82YR=2(B/B9N8G-P.VAT=' Z+R]W=W<N<VUI;&QA+G)U/"]A/CPO<#XB#0H)
M<F5S<&]N<V4N=W)I=&4@.(CPO=&0^(@.T*"7)E<W!O;G-E+G=R:71E("(\=&0@.
M8VQA<W,](B)N97<M=&0B(G9!;&EG;CTB(G1O<"(B(&)G0V]L;W(](B(C-6(Y
M,V%E(B(@.:&5I9VAT/2(B,3 B(CX\24U'(&AE:6=H=#TB(C$B(B!S<F,](B))
M4R]S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*"7)E<W!O;G-E+G=R
M:71E("(\+W1R/B(-"@.ER97-P;VYS92YW<FET92 B/'1R/B(-"@.ER97-P;VYS
M92YW<FET92 B/'1D('=I9'1H/2(B,3 P(B(@.8F=#;VQO<CTB(B-C8F4U838B
M(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O<VAI
M;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET92 B
M/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.8F=#;VQO<CTB(B-A-V)B
M.#8B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D('=I9'1H/2(B,C B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0]
M(B(Q(B(@.<W)C/2(B25-S:&EM+F=I9B(B('=I9'1H/2(B,2(B/CPO=&0^(@.T*
M"7)E<W!O;G-E+G=R:71E("(\=&0@.=D%L:6=N/2(B=&]P(B(@.=VED=&@.](B(U
M,# B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG:'0](B(Q(B(@.<W)C/2(B25,O
M<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D/B(-"@.ER97-P;VYS92YW<FET
M92 B/'1D(&-L87-S/2(B;F5W+71D+79E<G1I8V%L(B(@.=D%L:6=N/2(B=&]P
M(B(@.8F=#;VQO<CTB(B-C8F4U838B(B!H96EG:'0](B(R(B(^/$E-1R!H96EG
M:'0](B(Q(B(@.<W)C/2(B25,O<VAI;2YG:68B(B!W:61T:#TB(C$B(CX\+W1D
M/B(-"@.ER97-P;VYS92YW<FET92 B/"]T<CXB#0H)<F5S<&]N<V4N=W)I=&4@.
M(CPO=&%B;&4^(@.T*"7)E<W!O;G-E+G=R:71E("(\+V9O<FT^(@.T*"7)E<W!O
M;G-E+G=R:71E("(\2#$^(@.T*"7)E<W!O;G-E+G=R:71E("(\+T)/1%D^(@.T*
J"7)E<W!O;G-E+G=R:71E("(\+TA434P^(@.T*16YD(%-U8@.T*)3X-"@.T*
`
end
|||Thomas,
Take a look at a sample script I wrote for paging resultsets:
http://www.sqlservercentral.com/scri...tions/1233.asp
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CbZeXGrEHA.332@.TK2MSFTNGP14.phx.gbl...
> You are using the wrong approach for this.
> Here is some sample code that uses asp and getrows.
> Approaches like the one you are using do not offer the best performance.
> Check out
> http://www.adopenstatic.com/experime...dsetpaging.asp
> for more information.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Thomas Nielsen [AM Production A/S]" <jack_pot_dk@.h0tmail.com> wrote in
> message news:%23rHETFErEHA.2796@.TK2MSFTNGP10.phx.gbl...
> often
> the
> in-memory
> and
> to
> and
>
>

"Full-Text Indexing" is Grayed Out, Disabled

I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
database, except when I go into the SQL Server Enterprise Manager and
click on the "Tools" drop down menu in the tool bar, the option
"Full-Text Indexing" is grayed out (aka greyed out).
I think I need to start a service named MSSEARCH; however, when I go
into Start -> Settings -> Control Panel -> Administrative Tools ->
Services, I do not see any service whose name is close to "MSSEARCH".
I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
but the closest I got was an error message stating "Cannot get
full-text default path from registry."
I also installed SQL Server Service Pack 3.
When I execute stored procedure `sp_fulltext_database 'enable'`, I get
this error message:
"Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
Line 46
Full-Text Search is not installed, or a full-text component cannot be
loaded"
Therefore, how do I get the "Full-Text Indexing" to no longer be
grayed out?
Or, how to I install, start or implement full-text indexing?
Sounds like SQL FTS has not been installed.
Put your SQL Server cd on the cd platter.
double click setup.bat from the cd. click next.
select local computer, click next.
select upgrade, remove, or add components to an existing instance of SQL
Server, click next.
select the instance you wish to add SQL FTS to, click next.
select add components to your existing installation, click next.
In the component section give focus to Server Component (it should be given
focus by default).
In the sub components section, select full text search, click next. click
next, and accept all remaining defaults.
<charismatic_evangelist@.yahoo.com> wrote in message
news:dd8e1652.0409081147.21b78b87@.posting.google.c om...
> I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
> database, except when I go into the SQL Server Enterprise Manager and
> click on the "Tools" drop down menu in the tool bar, the option
> "Full-Text Indexing" is grayed out (aka greyed out).
> I think I need to start a service named MSSEARCH; however, when I go
> into Start -> Settings -> Control Panel -> Administrative Tools ->
> Services, I do not see any service whose name is close to "MSSEARCH".
> I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
> but the closest I got was an error message stating "Cannot get
> full-text default path from registry."
> I also installed SQL Server Service Pack 3.
> When I execute stored procedure `sp_fulltext_database 'enable'`, I get
> this error message:
> "Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
> Line 46
> Full-Text Search is not installed, or a full-text component cannot be
> loaded"
> Therefore, how do I get the "Full-Text Indexing" to no longer be
> grayed out?
> Or, how to I install, start or implement full-text indexing?
|||C_E,
It is always a good idea to know what SQL Server version as well as Edition
is as the Full-Text Search (FTS) components are not supported on all SQL
Server 2000 Editions. See SQL Server 2000 Books Online (BOL) title "Features
Supported by the Editions of SQL Server 2000" as the "Microsoft Search
(MSSearch.exe) service is installed only with certain SQL Server editions.
Could you post the full output of the following SQL script?
select @.@.version
Installing SQL Server Service Pack 3 alone will not resolve this issue as
you will need to install the FTS components (if supported) and then
re-install SQL Server Service Pack 3 to fix any FTS bugs, issues, etc.
Depending upon the output of the sql script, you may be able to install the
FTS components from your SQL 2000 installation CD via custom installation
and select Full-Text Search under the server components list.
Regards,
John
<charismatic_evangelist@.yahoo.com> wrote in message
news:dd8e1652.0409081147.21b78b87@.posting.google.c om...
> I want to enable "Full-Text Indexing" in my Microsoft SQL Server 2000
> database, except when I go into the SQL Server Enterprise Manager and
> click on the "Tools" drop down menu in the tool bar, the option
> "Full-Text Indexing" is grayed out (aka greyed out).
> I think I need to start a service named MSSEARCH; however, when I go
> into Start -> Settings -> Control Panel -> Administrative Tools ->
> Services, I do not see any service whose name is close to "MSSEARCH".
> I also tried `sqlftwiz` (SQL Full-Text Wizard) with several options,
> but the closest I got was an error message stating "Cannot get
> full-text default path from registry."
> I also installed SQL Server Service Pack 3.
> When I execute stored procedure `sp_fulltext_database 'enable'`, I get
> this error message:
> "Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database,
> Line 46
> Full-Text Search is not installed, or a full-text component cannot be
> loaded"
> Therefore, how do I get the "Full-Text Indexing" to no longer be
> grayed out?
> Or, how to I install, start or implement full-text indexing?
|||Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
tried the steps that you gave below. The instance of the Server that I
applied it to was the (local)(Windows NT) server, where my database resides.
Even after going through all the steps, and having turned SQL FTS on, the
full text search is still greyed out when I right click on the particular
table I need to be able to search. Any other ideas of what to do?
- MacL
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Sounds like SQL FTS has not been installed.
> Put your SQL Server cd on the cd platter.
> double click setup.bat from the cd. click next.
> select local computer, click next.
> select upgrade, remove, or add components to an existing instance of SQL
> Server, click next.
> select the instance you wish to add SQL FTS to, click next.
> select add components to your existing installation, click next.
> In the component section give focus to Server Component (it should be
> given
> focus by default).
> In the sub components section, select full text search, click next. click
> next, and accept all remaining defaults.
>
|||Mac,
While you may have the same *symptoms* as Charismatic_Evangelist, you may or
may not have the same problem, especially as s/he never provided his/her SQL
Server 2000 version & edition info. It is always a good idea to know what
SQL Server version as well as Edition is as the Full-Text Search (FTS)
components are not supported on all SQL Server 2000 Editions. See SQL Server
2000 Books Online (BOL) title "Features Supported by the Editions of SQL
Server 2000" as the "Microsoft Search (MSSearch.exe) service is installed
only with certain SQL Server editions. Could you post the full output of the
following SQL script?
select @.@.version
You should also review the following KB article Q270671 (Q270671) "PRB: Full
Text Search Menus Are Not Enabled for Local Windows NT Accounts" at
http://support.microsoft.com/default...;en-us;q270671 depending
upon how you have the MSSQLServer service startup account setup.
If you did recently upgrade or install the FTS components, could you also
attach the following SQL setup log files: sqlstp.log (usually located at
C:\WINNT\Temp) and SearchSetup.log usually located at C:\WINNT\. These files
can also help in troubleshooting FTS installation issues.
Regards,
John
"Mac Littlefield" <warrenlittlefield@.sbcglobal.net> wrote in message
news:#Ai13dnpEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
> tried the steps that you gave below. The instance of the Server that I
> applied it to was the (local)(Windows NT) server, where my database
resides.[vbcol=seagreen]
> Even after going through all the steps, and having turned SQL FTS on, the
> full text search is still greyed out when I right click on the particular
> table I need to be able to search. Any other ideas of what to do?
> - MacL
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
click
>
|||There can be serveral causes of the Full-Text Index Table menu option not
being enabled
a.. SQL FTS is not enabled on this SQL server. Click here for instructions
on how to install SQL FTS on your SQL Server.
a.. You are not in the dbo role for this database
a.. The System Administrator role is not in the Local Administrators group
for this machine or the NTAuthority\Logon group is not in the System
Administrators group.
If you remove BUILTIN\Administrators and Full Text is enabled, add a login
account for the Local System account using sp_grantlogin [NT
Authority\System], and ensure that the login account is a member of the
sysadmin fixed server role. Here is some sample TSQL code to do this:
if not exists (select * from master.dbo.syslogins where loginname = N'NT
Authority\System')
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System', N'us_english'
GO
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
GO
a.. Something else yet undetermined. Microsoft acknowledges this problem in
this kb article
(http://support.microsoft.com/default...1&Product=sql2
k) and recommends that you use the full text stored procedures to manage
your catalog or tables.
When I run across this problem (I don't run across is often as typically I
use the full text stored procedures) I normally use the SQLFTSWiz.exe
program which can be found by default in: C:\Program Files\Microsoft SQL
Server\80\Tools\Binn you can launch it by opening a command prompt and
typing for NT Authentication:
sqlftwiz /n /sSQLServerName[\InstanceName] /dpubs
or for SQL Authentication:
sqlftwiz /sSQLServerName[\InstanceName] /dpubs /uAccount /pPassword
In both of the above examples, SQLServerName[\InstanceName] is the name of
the SQL Server you wish to connect to. If you are using the default instance
you would use the below:
sqlftwiz /sSQLServerName /dpubs /uAccount /pPassword
If you are using an instance, other than the default instance you would use
the below (where InstanceName is the name of the instance you are using):
sqlftwiz /sSQLServerName\InstanceName /dpubs /uAccount /pPassword
Pubs is the database you wish to create your full text table in.
The dialogs you get are for the most part identical to the dialogs you would
get if you were to just right click on a table. After the initial splash
screen select the table you wish to index. The rest of the dialogs are
identical to the dialogs you will see if you were to right click on a table
in EM and select Full Text Index table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mac Littlefield" <warrenlittlefield@.sbcglobal.net> wrote in message
news:%23Ai13dnpEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Given that I had the same problem as charismatic_evangelist@.yahoo.com, I
> tried the steps that you gave below. The instance of the Server that I
> applied it to was the (local)(Windows NT) server, where my database
resides.[vbcol=seagreen]
> Even after going through all the steps, and having turned SQL FTS on, the
> full text search is still greyed out when I right click on the particular
> table I need to be able to search. Any other ideas of what to do?
> - MacL
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ukjtrBglEHA.2764@.TK2MSFTNGP11.phx.gbl...
click
>

"Full-Text Index Table" is disabled?

I'm trying to do some local development so I installed Full-Text Indexing as
part of the SQL 2000 Developer Edition on my Windows XP machine.
The MS Search Service is running but the option is still disabled in EM when
I right-click on a table.
Thanks, Dave.
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BE6EABAB-0F45-40B2-9C9C-1A29A1A8746B@.microsoft.com...
> I'm trying to do some local development so I installed Full-Text Indexing
> as
> part of the SQL 2000 Developer Edition on my Windows XP machine.
> The MS Search Service is running but the option is still disabled in EM
> when
> I right-click on a table.
> Thanks, Dave.