Newbie Question on searching text

I have a bulletin board application (written by a programmer) and want to
allow users to search the database for keywords/phrases. Doing a basic
search eats up the cpu like crazy, and I know there are some strategies on
how to get this done right.
Can someone provide me with an overview of how best to go about this? If
there are good articles/tutorials on this I'd greatly appreciate it also.
Btw, I'm running MS SQL server 2000 running dotnet.
Shabam,
A good place to start to understand SQL Server 2000 Full-text Search (FTS)
is Books Online (BOL) titles: "Full-Text Query Architecture", "Maintaining
Full-Text Indexes", "Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued
Functions" and especially "Full-Text Search Recommendations". You can also
search the BOL using "full text" (include the double quotes) via the BOL
Search tab for additional titles.
When you state that your "basic search" eats up CPU like crazy, are you
currently using FTS or are you using T-SQL LIKE or some other method? I've
also attached a SQL script file (Full Text Population Example.sql) that
demonstrates all aspects of using SQL FTS on the pubs database table
pub_info.
If you have additional questions, please post them.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
begin 666 Full Text Population Example.sql
M#0HM+0T*+2TM(%1O($5N86)L92!T:&4@.4'5B<R!$871A8F%S9 2!F;W(@.1G5L
M;"U497AT#0HM+0T*=7-E('!U8G,-"F=O#0IS<%]F=6QL=&5X=%]S97)V:6-E
M("=C;&5A;E]U<"<-"F=O#0IS<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE
M)R M+2 M+3X@.3D]413H@.3VYL>2!R=6X@.=&AI<R!/3D-%('!E<B!D871A8F%S
M92 A(2$-"F=O#0H-"BTM#0HM+2T@.5&\@.0W)E871E+U)E;6]V92!T:&4@.17AI
M<W1I;F<@.1G5L;"U497AT(%1A8FQE($EN9&5X+"!#871A;&]G( T*+2T@.(" @.
M268@.1G5L;"U497AT($EN9&5X(&5X:7-T<RP@.1%)/4"!T:&%T($EN9&5X+ T*
M+2T@.(" @.268@.1G5L;"U497AT($EN9&5X(&1O97,@.;F]T(&5X:7-T+"!#4D5!
M5$4@.=&AA="!);F1E>"X-"BTM#0IU<V4@.<'5B<PT*9V\-"DE&($]"2D5#5%!2
M3U!%4E19("@.@.;V)J96-T7VED*"=P=6)?:6YF;R<I+"=486)L94AA<T%C=&EV
M949U;&QT97AT26YD97@.G*2 ](#$-"D)%1TE.#0H@.(" @.<')I;G0@.)U1A8FQE
M('!U8E]I;F9O(&ES($9U;&PM5&5X="!%;F%B;&5D+"!D<F]P<&EN9R!&=6QL
M+51E>'0@.26YD97@.@.)B!#871A;&]G+BXN)PT*(" @.($5814,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)W!U8E]I;F9O)RP@.)V1R;W G#0H@.(" @.15A%0R!S<%]F=6QL
M=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V1R;W G#0I%3D0-"D5,4T4@.248@.
M3T)*14-44%)/4$525%D@.*"!O8FIE8W1?:60H)W!U8E]I;F9O)RDL)U1A8FQE
M2&%S06-T:79E1G5L;'1E>'1);F1E>"<I(#T@., T*0D5'24X-"B @.("!P<FEN
M=" G5&%B;&4@.<'5B7VEN9F\@.:7,@.3D]4($9U;&PM5&5X="!%;F%B;&5D+"!C
M<F5A=&EN9R!&5"!#871A;&]G+"!);F1E>" F($%C=&EV871I;F<N+BXG#0H@.
M(" @.15A%0R!S<%]F=6QL=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V-R96%T
M92<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P=6)?:6YF;R<L("=C
M<F5A=&4G+" G4'5B26YF;R<L("=54$M#3%]P=6)I;F9O)PT*(" @.($5814,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P=6)?:60G+" G861D
M)PT*(" @.($5814,@.<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P
M<E]I;F9O)RP@.)V%D9"<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P
M=6)?:6YF;R<L("=A8W1I=F%T92<-"D5.1 T*#0H-"BTM#0HM+2T@.069T97(@.
M16YA8FQI;F<@.)B!!8W1I=F%T:6YG(%1A8FQE<RP@.0V]L=6UN<R F($EN9&5X
M97,@.+2!3=&%R="!&=6QL(%!O<'5L871I;VX-"BTM#0IU<V4@.<'5B<PT*9V\-
M"D)%1TE.#0I3150@.3D]#3U5.5"!/3@.T*1$5#3$%212! 8F5G:6X@.9&%T971I
M;64-"D1%0TQ!4D4@.0&5N9"!D871E=&EM90T*4T54($!B96=I;B ]($-54E)%
M3E1?5$E-15-404U0#0I%6$5#('-P7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN
M9F\G+" G<W1A<G1?9G5L;"<@.+2T@.(D9U;&P@.0W)A=VPB#0HM+2!%6$5#( '-P
M7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN9F\G+" G<W1A<G1?:6YC<F5M96YT
M86PG("TM("));F-R96UE;G1A;"!#<F%W;"(-"BTM#0HM+2T@.5V%I="!F;W(@.
M8W)A=VP@.=&\@.8V]M<&QE=&4-"BTM#0I$14-,05)%($!S=&%T=7,@.:6YT+"!
M:71E;4-O=6YT(&EN="P@.0&ME>4-O=6YT(&EN="P@.0&EN9&5X4VEZ92!I;G0-
M"E-%3$5#5"! <W1A='5S(#T@.1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U
M8DEN9F\G+" G<&]P=6QA=&5S=&%T=7,G*0T*5TA)3$4@.*$!S=&%T=7,@./#X@.
M,"D-"D)%1TE.#0H@.(%=!251&3U(@.1$5,05D@.)S P.C P.C Q)R M+2!W86ET
M(&9O<B Q('-E8V]N9"!B969O<F4@.8VAE8VMI;F<@.1E0@.4&]P=6QA=&5S=&%T
M=7,N+BX-"B @.4T5,14-4($!S=&%T=7,@./2!&=6QL5&5X=$-A=&%L;V=0<F]P
M97)T>2@.G4'5B26YF;R<L("=P;W!U;&%T97-T871U<R<I#0I%3D0-"E-%5"!
M96YD(#T@.0U524D5.5%]424U%4U1!35 -"E=!251&3U(@.1$5,05D@.)S P.C P
M.C$U)R M+2!W86ET(&9O<B Q-2!S96-O;F1S(&EN(&]R9&5R('1O(&=E="!C
M;W)R96-T($94(%!R;W!E<G1Y(&EN9F\N+BX-"E-%5"! :71E;4-O=6YT(#T@.
M1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U8DEN9F\G+" G:71E;6-O=6YT
M)RD-"E-%5"! :V5Y0V]U;G0@./2!&=6QL5&5X=$-A=&%L;V=0<F]P97)T>2@.G
M4'5B26YF;R<L("=U;FEQ=65K97EC;W5N="<I#0I3150@.0&EN9 &5X4VEZ92 ]
M($9U;&Q497AT0V%T86QO9U!R;W!E<G1Y*"=0=6));F9O)RP@.) VEN9&5X<VEZ
M92<I#0I04DE.5"!#3TY615)4*&-H87(H,S I+"! 8F5G:6XL(#DI("L@.8VAA
M<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.0&5N9"P@..2D@.*R!C
M:&%R*# Y*2 K#0H@.(" @.("!#3TY615)4*&-H87(H,S I+"! 96YD("T@.0&)E
M9VEN+" X*2 K(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H8VAA<B@.S,"DL
M($1!5$5$249&("AH:"P@.0&)E9VEN+"! 96YD*2D@.*R!C:&%R*# Y*2 K#0H@.
M(" @.("!#3TY615)4*&-H87(H,S I+"!$051%1$E&1B H;6DL($!B96=I;BP@.
M0&5N9"DI("L@.8VAA<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.
M1$%4141)1D8@.*'-S+"! 8F5G:6XL($!E;F0I*2 K(&-H87(H,#DI("L-"B @.
M(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I=&5M0V]U;G0I("L@.8VAA<B@.P
M.2D@.*PT*(" @.(" @.0T].5D525"AV87)C:&%R*#$P*2P@.0&ME>4-O=6YT*2 K
M(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I;F1E
M>%-I>F4I#0I3150@.3D]#3U5.5"!/1D8-"D5.1 T*9V\-"@.T*+2T-"BTM+2!#
M;VYF:7)M(&%B;W9E(')E<W5L=',@.=VET:#H-"BTM#0I314Q%0U0@.<'5B7VED
M+"!P<E]I;F9O( T*"4923TT@.<'5B7VEN9F\@.5TA%4D4@.0T].5$%)3E,H<')?
8:6YF;RP@.)R)B;V]K*B(G*0T*9V\-"@.T*
`
end
|||can you post your query here?
Also is this an English language search?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
sql

Newbie question on BULK INSERT of text file

A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:

> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:

> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql

Newbie question about sprocs

When I run my stored proceedure in query analyzer and copy the text to the pane in the report designer, all is well, but if I run the same stored proceedure as a stored procedure in the report designer it doesn't return all the fields that it does as text. What's the difference? What do I do to fix this?Two things come to mind. Ownership and location of the stored procedure.
OWNERSHIP:
Objects like tables, views, and stored procedures can be owned by different
logins.
Lets say you have two users in your database
user1
user2
They can both create a table called FOO
Each table can have different columns.
Each user can also create a stored procedure named procFOO
These stored procedures can return different columns.
LOCATION OF THE STORED PROCEDURE:
It is possible to create stored procedures in multiple databases. For
example, procFOO within northwind could select from the Products table and
procFOO within pubs could select from the authors table. Are you calling
the stored procedure from the database that you created it in (and are
running it from Query Analyzer)?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> When I run my stored proceedure in query analyzer and copy the text to the
pane in the report designer, all is well, but if I run the same stored
proceedure as a stored procedure in the report designer it doesn't return
all the fields that it does as text. What's the difference? What do I do to
fix this?|||So I hit the nail on the head; was it an ownership or a location issue?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8EC3378E-037A-4C6A-93BE-7367603FDBE3@.microsoft.com...
> Thanks you were a great help. All is well!
> "Keith Kratochvil" wrote:
> > Two things come to mind. Ownership and location of the stored
procedure.
> >
> > OWNERSHIP:
> > Objects like tables, views, and stored procedures can be owned by
different
> > logins.
> > Lets say you have two users in your database
> > user1
> > user2
> > They can both create a table called FOO
> > Each table can have different columns.
> > Each user can also create a stored procedure named procFOO
> > These stored procedures can return different columns.
> >
> > LOCATION OF THE STORED PROCEDURE:
> > It is possible to create stored procedures in multiple databases. For
> > example, procFOO within northwind could select from the Products table
and
> > procFOO within pubs could select from the authors table. Are you
calling
> > the stored procedure from the database that you created it in (and are
> > running it from Query Analyzer)?
> >
> > --
> > Keith
> >
> >
> > "Arly" <Arly@.discussions.microsoft.com> wrote in message
> > news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> > > When I run my stored proceedure in query analyzer and copy the text to
the
> > pane in the report designer, all is well, but if I run the same stored
> > proceedure as a stored procedure in the report designer it doesn't
return
> > all the fields that it does as text. What's the difference? What do I do
to
> > fix this?
> >
> >

NEWBIE AND EASY QUESTION

Hi,
Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
it's possible (and How :)) i can find a string of text in all tables of a
SQL Database.
I explain my problem for easy understanding:
I have a form that submit data in to a SQL database and I want to know
in wich table does it goes... I inserted a simple text on a form's field
(ex. ABCDEFG) now I want to search every table until i find that string.
Thanks,This isn't that easy to do as it might sound. I suggest you run Profiler and catch the statement the
app submits and inspect that INSERT statement. In return, you will learn to use the very valuable
Profiler tool!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joao Bras - Brasmatica" <no@.spam.com> wrote in message
news:%237fAUjNoEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
> it's possible (and How :)) i can find a string of text in all tables of a
> SQL Database.
> I explain my problem for easy understanding:
> I have a form that submit data in to a SQL database and I want to know
> in wich table does it goes... I inserted a simple text on a form's field
> (ex. ABCDEFG) now I want to search every table until i find that string.
> Thanks,
>|||This should help you:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
David Portas
SQL Server MVP
--|||Thank you for all the answers. Special Thanks for David Portas (are you
portuguese?) It worked wonderfully. Tibor Karaszi, thank you, for your
promptly help.

NEWBIE AND EASY QUESTION

Hi,
Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
it's possible (and How ) i can find a string of text in all tables of a
SQL Database.
I explain my problem for easy understanding:
I have a form that submit data in to a SQL database and I want to know
in wich table does it goes... I inserted a simple text on a form's field
(ex. ABCDEFG) now I want to search every table until i find that string.
Thanks,
This isn't that easy to do as it might sound. I suggest you run Profiler and catch the statement the
app submits and inspect that INSERT statement. In return, you will learn to use the very valuable
Profiler tool!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joao Bras - Brasmatica" <no@.spam.com> wrote in message
news:%237fAUjNoEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Sorry for my poor english. I'm a newbie in SQL Server, and I want to know if
> it's possible (and How ) i can find a string of text in all tables of a
> SQL Database.
> I explain my problem for easy understanding:
> I have a form that submit data in to a SQL database and I want to know
> in wich table does it goes... I inserted a simple text on a form's field
> (ex. ABCDEFG) now I want to search every table until i find that string.
> Thanks,
>
|||This should help you:
http://vyaskn.tripod.com/search_all_...all_tables.htm
David Portas
SQL Server MVP
|||Thank you for all the answers. Special Thanks for David Portas (are you
portuguese?) It worked wonderfully. Tibor Karaszi, thank you, for your
promptly help.