Getting error MS SQL2000: query

显示标签为“query”的博文。显示所有博文

2012年3月29日星期四

Fulldatabase backup query

SQL Server full backup displays the following message on successful
completion of the full database backup.
Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
MB/sec).
I understand SQL Server manages data files in the form of pages and thats
the reason on completion of backup it display message "Processed 1136 pages
for database 'MyDB', file 'MyDB' on file 1". But why does it display message
"Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
transaction log files are not managed in the form of pages.Hi
When BACKUP started , it reads (backups) all data pages and last step it
goes to LOG file and reads/backups all the data in the LOG from the point
the BACKUP was started
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136
> pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display
> message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when
> the
> transaction log files are not managed in the form of pages.
>|||The backup database process includes the log records produced while the back
up was running. When you
do RESTORE, the log records are used to give you a consistent view of the da
ta (by doing REDO and
UNDO of those log records, just as each time the database is started),.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136 page
s
> for database 'MyDB', file 'MyDB' on file 1". But why does it display messa
ge
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when th
e
> transaction log files are not managed in the form of pages.
>

Fulldatabase backup query

SQL Server full backup displays the following message on successful
completion of the full database backup.
Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
MB/sec).
I understand SQL Server manages data files in the form of pages and thats
the reason on completion of backup it display message "Processed 1136 pages
for database 'MyDB', file 'MyDB' on file 1". But why does it display message
"Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
transaction log files are not managed in the form of pages.Hi
When BACKUP started , it reads (backups) all data pages and last step it
goes to LOG file and reads/backups all the data in the LOG from the point
the BACKUP was started
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136
> pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display
> message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when
> the
> transaction log files are not managed in the form of pages.
>|||The backup database process includes the log records produced while the backup was running. When you
do RESTORE, the log records are used to give you a consistent view of the data (by doing REDO and
UNDO of those log records, just as each time the database is started),.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136 pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
> transaction log files are not managed in the form of pages.
>

full text soundex problem

I have a fulltext search in my query i am using containstable function .I have to search for keywords like '170st'.but it is returning all the strings near to it, like 168st, 200st,110st.I think it is taking the soundex function.
Please help me
Pradeep K V
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
pradeep wrote on Fri, 15 Jul 2005 00:09:36 -0700:

> I have a fulltext search in my query i am using containstable function .I
> have to search for keywords like '170st'.but it is returning all the
> strings near to it, like 168st, 200st,110st.I think it is taking the
> soundex function. Please help me
I don't think FTS ever uses soundex. Maybe it's using the noise word list to
remove all the numbers - that way they're all just indexed as 'st'. Have you
made any changes to the noise words file?
Dan
|||Pradeep,
Daniel is correct Full-text Search (FTS) does not use SOUNDEX directly, but
it can be used in combination with SOUNDEX. Additionally, you may want to
review the following links as well as the below TSQL examples of combining
CONTAINS & SOUNDEX:
You may want to look at some of the improved soundex algorithms as well as
the Levenshtein Distance algorithm You should be able to search Google to
find more code examples, for example: 'METAPHONE soundex "sql server" fuzzy
name search' and I quickly found - "Double Metaphone Sounds Great" at
http://www.winnetmag.com/Article/Art...094/26094.html You can freely
download the code in a zip file that has several a user-defined function
(UDF) that implement Double Metaphone.
Below are some additional SOUNDEX links:
http://www.merriampark.com/ld.htm
http://www.bcs-mt.org.uk/nala_006.htm
Could you post the SQL FTS query you are using to get the results you are
seeing along with the full output of -- SELECT @.@.version -- as this will
provide more information on why you are getting these results.
use pubs
-- Combined SOUNDEX OR CONTAINS query that Searches for names that sound
like "Michael".
select au_lname, au_fname FROM authors -- returns 2 rows
where contains(au_fname, 'Mich*') or SOUNDEX(au_fname) = 'M240'
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:#4oNygTiFHA.1464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> pradeep wrote on Fri, 15 Jul 2005 00:09:36 -0700:
..I
> I don't think FTS ever uses soundex. Maybe it's using the noise word list
to
> remove all the numbers - that way they're all just indexed as 'st'. Have
you
> made any changes to the noise words file?
> Dan
>

Full text slow startup on SQL 2005 x64, Win2003 x64

Hi there,
We have a problem with a slow startup of the SQL 2005 full text engine on a
new box. The first query (after a reboot or first query in the morning)
against the full text index is very slow, it takes around 30 seconds to
execute. The full text index is very small (~2 mb), so this should not be an
issue.
On another server with the same database we don't see this problem. The
first query is executed in less than a second.
The only difference between the servers is that the server with the slow
full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
The other server is running SQL 2005 Standard and Windows 2003 Standard.
Any suggestions?
I was just about to do a post for the same thing. I'm seeing this same
problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
Server 2005 Enterprise and one is using 2005 Developer; both machines have
2GB RAM. The first query using FTS that I run takes ~45 seconds and then
every one after that is very fast (< 1 second), for about 10 or 15 minutes.
Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
thing again. Both machines have the same database (one's a development copy),
and the table with the index is only about 60MB, with ~40K rows.
Any help would be much appreciated.
thanks,
michael
"Rasmus Waeherns" wrote:

> Hi there,
> We have a problem with a slow startup of the SQL 2005 full text engine on a
> new box. The first query (after a reboot or first query in the morning)
> against the full text index is very slow, it takes around 30 seconds to
> execute. The full text index is very small (~2 mb), so this should not be an
> issue.
> On another server with the same database we don't see this problem. The
> first query is executed in less than a second.
> The only difference between the servers is that the server with the slow
> full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
> The other server is running SQL 2005 Standard and Windows 2003 Standard.
> Any suggestions?
>
>
|||Hi there,
We tried everything, from reinstalling SQL Server 2005, disabling services,
restoring new databases, rebuilding full text index - you name it, and
didn't find a solution for the problem.
We've now reinstalled Windows 2003 x64 and SQL 2005 x64 on the server and
now the problems are gone. Weird!
Best regards,
Rasmus Waehrens
"ackphht" <ackphht@.discussions.microsoft.com> wrote in message
news:1B500AE2-5EBD-461C-90FE-6B1594D0B9F8@.microsoft.com...[vbcol=seagreen]
>I was just about to do a post for the same thing. I'm seeing this same
> problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
> Server 2005 Enterprise and one is using 2005 Developer; both machines have
> 2GB RAM. The first query using FTS that I run takes ~45 seconds and then
> every one after that is very fast (< 1 second), for about 10 or 15
> minutes.
> Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
> thing again. Both machines have the same database (one's a development
> copy),
> and the table with the index is only about 60MB, with ~40K rows.
> Any help would be much appreciated.
> thanks,
> michael
> "Rasmus Waeherns" wrote:

2012年3月27日星期二

Full Text Search without any result

Dear all,
I've build a FullText Indexing at Enterprise Manager. However, when I run
the following query, it does not have any result return:
select u_documenttype, u_document, u_filename from doclib where
freetext(u_document, ' "category" or "eis" ')
Does anyone have any idea about my situation?
Thanks
Best Regards,
Eilsa
One more thing, the "u_document" is an image type
thanks
"Eilsa" <asd@.asd.asd> wrote in message
news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've build a FullText Indexing at Enterprise Manager. However, when I run
> the following query, it does not have any result return:
> select u_documenttype, u_document, u_filename from doclib where
> freetext(u_document, ' "category" or "eis" ')
> Does anyone have any idea about my situation?
>
> Thanks
> Best Regards,
> Eilsa
>
|||Eilsa,
Yes, I do... Could you post the full output of the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help doclib
go
Additionally, what is the language of the text in the documents (MS Word
files) that you are storing in column "u_document" and how did you import or
upload these files to SQL Server? Both questions as well as the datatype and
size of the file extension column in your table doclib will provide clues to
answer your question.
Regards,
John
"Eilsa" <asd@.asd.asd> wrote in message
news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> One more thing, the "u_document" is an image type
> thanks
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
run
>
|||us_english
(1 row(s) affected)
Microsoft SQL Server 2000-8.00.194(IntelX86)
Aug620000:57:48
Copyright(c)1988-2000 Microsoft Corporation
DeveloperEditionon Windows NT5 (Build2195:00:00 Service Pack 4)
(1 row(s) affected)
NameOwnerTypeCreated_datetime
DocFiledbouser table8/9/2004 06:23.5
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
------
File_IDintno4100no(n/a)(n/a)NULL
Fileimageno16yes(n/a)(n/a)NULL
FileTypecharno3yesnoyesSQL_Latin1_General_CP1_CI_AS
IdentitySeedIncrementNot ForReplication
No identity column defined.NULLNULLNULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_nameindex_descriptionindex_keys
-----
PK_File clustered, unique,primary key located on PRIMARYFile_ID
constraint_typeconstraint_namedelete_actionupdate_actionstatus_enabledstatus_for_replicationconstraint_keys
------
PRIMARYKEY(clustered)PK_File(n/a)(n/a)(n/a)(n/a)File_ID
No foreign keysreference this table.
Table is referenced by views
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Eilsa,
> Yes, I do... Could you post the full output of the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> EXEC sp_help doclib
> go
> Additionally, what is the language of the text in the documents (MS Word
> files) that you are storing in column "u_document" and how did you import or
> upload these files to SQL Server? Both questions as well as the datatype and
> size of the file extension column in your table doclib will provide clues to
> answer your question.
> Regards,
> John
>
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> run
|||Thanks, Bob,
You're using SQL Server 2000 (RTM) Developer Edition on Windows 2000. You're
table DocFile and your file extension column is FileType char(3) and
Nullable. However, I still need additional info, specifically what is the
language of the text in the documents (MS Word files) that you are storing
in column "u_document" and how did you import or upload these files to SQL
Server?
I've attached a SQL script file (Import_FTS_Images.sql) that uses TextCopy
to import documents as well as html pages into a test table FTSTable that
also has a file extension column (ExtCol) defined as char(3) and Nullable
and this configuration as worked for years with US_English documents. Test
your document upload into this configuration and then run a Full Population
and test FTS queries. If you still have a problem getting the expected
results, please post the exact SQL query as well as the exact sample data
contained in the document/html pages.
Thanks,
John
"Bob" <kcsc1@.hotpop.com> wrote in message
news:ef3b388e.0408100822.603f67f2@.posting.google.c om...
> us_english
> (1 row(s) affected)
> Microsoft SQL Server 2000-8.00.194 (Intel X86)
> Aug 6 2000 0:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Developer Edition on Windows NT5 (Build 2195:00:00 Service Pack 4)
> (1 row(s) affected)
> Name Owner Type Created_datetime
> DocFile dbo user table 8/9/2004 06:23.5
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
> ----

> File_ID int no 4 10 0 no (n/a) (n/a) NULL
> File image no 16 yes (n/a) (n/a) NULL
> FileType char no 3 yes no yes SQL_Latin1_General_CP1_CI_AS
>
> Identity Seed Increment Not For Replication
> ----
--
> No identity column defined. NULL NULL NULL
>
> RowGuidCol
> --
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> --
> PRIMARY
>
> index_name index_description index_keys
> ----
--
> PK_File clustered, unique,primary key located on PRIMARY File_ID
>
> constraint_type constraint_name delete_action update_action status_enabled
status_for_replication constraint_keys
> ----

> PRIMARY KEY (clustered) PK_File (n/a) (n/a) (n/a) (n/a) File_ID
>
> No foreign keys reference this table.
> Table is referenced by views
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
import or[vbcol=seagreen]
and[vbcol=seagreen]
clues to[vbcol=seagreen]
I[vbcol=seagreen]
begin 666 Import_FTS_Images.sql
M#0IU<V4@.<'5B<PT*9V\-"FEF(&5X:7-T<R H<V5L96-T("H@.9G)O;2!S>7-O
M8FIE8W1S('=H97)E(&ED(#T@.;V)J96-T7VED*"=&5%-486)L92<I*0T*("!D
M<F]P('1A8FQE($944U1A8FQE#0IG;PT*#0I#4D5!5$4@.5$%"3$4@.1 E135&%B
M;&4@.* T*("!+97E#;VP):6YT($E$14Y42519("@.Q+#$I($Y/5"!.54Q,( T*
M(" @.($-/3E-44D%)3E0@.1E135&%B;&5?2418(%!224U!4ED@.2T59($-,55-4
M15)%1"P-"B @.5&5X=$-O; ET97AT($Y53$PL#0H@.($EM86=E0V]L"6EM86=E
M($Y53$PL#0H@.($5X=$-O; EC:&%R*#,I($Y53$PL("TM(&-A;B!B92!E:71H
M97(@.<WES;F%M92!O<B!C:&%R*#,I#0H@.(%1I;653=&%M<$-O; ET:6UE<W1A
M;7 @.3E5,3" -"BD@.3TX@.6U!224U!4EE=#0IG;PT*#0H-"BTM($EN<V5R="!D
M871A+BXN("A.;W1E.B!);FET86QI>FEN9R!)34%'12!C;VQU; 6X@.=VET:" P
M>$9&1D9&1D9&(&9O<B!U<V4@.=VET:"!497AT0V]P>2YE>&4I#0I)3E-%4E0@.
M1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W(#$G+" P
M>$9&1D9&1D9&+" G9&]C)RP@.3E5,3"D-"DE.4T525"!&5%-486)L92!V86QU
M97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.,B<L(#!X1D9&1 D9&1D8L("=X
M;',G+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A;'5E<R@.G5 &5S="!415A4
M($1A=&$@.9F]R(')O=R S)RP@.,'A&1D9&1D9&1BP@.)W!P="<L($Y53$PI#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#0G+" P>$9&1D9&1D9&+" G='AT)RP@.3E5,3"D-"DE.4T525"!&5%-486)L
M92!V86QU97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.-2<L(#!X1D9&1D9&
M1D8L("=H=&TG+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A; '5E<R@.G5&5S
M="!415A4($1A=&$@.9F]R(')O=R V)RP@.,'A&1D9&1D9&1BP@.)W!D9B<L($Y5
M3$PI#0I)3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F
M;W(@.<F]W(#<G+" P>$9&1D9&1D9&+" G>&UL)RP@.3E5,3"D-"F=O#0H-"@.T*
M+2T@.4V5L96-T(&1A=&$-"E-%3$5#5" J(&9R;VT@.1E135&%B;&4-"F=O#0H-
M"@.T*9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.2%1-
M3%]F:6QE+FAT;2!I;G1O(%)O=R U("$A#0HM+2!.3U1%.B!%;G-U<F4@.=&AE
M(&-O<G)E8W0@.<&%T:"!F;W(@.=&5X=&-O<'DN97AE(2$-"G-E="! <75E<GD@.
M/2 G1#I<35-344PX,%Q-4U-13"1344PX,%Q":6YN7'1E>'1C;W!Y("]S("<K
M0$!S97)V97)N86UE*R<@.+W4@.<V$@.+W @.+V0@.<'5B<R O="!&5%-486)L92 O
M8R!);6%G94-O;" O9B!$.EQ344Q&:6QE<UQ3:&EL;VA</$A434Q?9FEL93XN
M:'1M("]I("]K(#4P,# @.+W<@.(G=H97)E($ME>4-O;#TU(B<-"G!R:6YT($!Q
M=65R>0T*97AE8R!M87-T97(N+GAP7V-M9'-H96QL($!Q=65R>0T*9V\-"@.T*
M9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.35-?5V]R
M9"YD;V,@.:6YT;R!2;W<@.,2 A(0T*<V5T($!Q=65R>2 ]("=$.EQ-4U-13#@.P
M7$U34U%,)%-13#@.P7$)I;FY<=&5X=&-O<'D@.+W,@.)RM 0'-E<G9E<FYA;64K
M)R O=2!S82 O<" O9"!P=6)S("]T($944U1A8FQE("]C($EM86=E0V]L("]F
M($0Z7$I+86YE7$1O8W-</$U37U=O<F0^+F1O8R O:2 O:R U,# P("]W(")W
M:&5R92!+97E#;VP],2(G#0IP<FEN="! <75E<GD-"F5X96,@.;6%S=&5R+BYX
M<%]C;61S:&5L;"! <75E<GD-"F=O#0H-"BTM(%-E;&5C="!D871A#0I314Q%
M0U0@.*B!F<F]M($944U1A8FQE#0IG;PT*#0H-"BTM($9422 -"G5S92!P=6)S
M#0IG;PT*97AE8R!S<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE)R -"F=O
M#0IE>&5C('-P7V9U;&QT97AT7W-E<G9I8V4@.)V-L96%N7W5P)PT*#0HM+2!$
M<F]P($9422P@.:68@.;F5C97-S87)Y+BXN#0IE>&5C('-P7V9U;&QT97AT7W1A
M8FQE("=&5%-486)L92<L)V1R;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L
M;V<@.)T944T-A=&%L;V<G+"=D<F]P)PT*#0H-"F5X96,@.<W!?9G5L;'1E>'1?
M8V%T86QO9R G1E130V%T86QO9R<L)V-R96%T92<-"F5X96,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)T944U1A8FQE)RPG8W)E871E)RPG1E130V%T8 6QO9R<L)T94
M4U1A8FQE7TE$6"<-"F5X96,@.<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L
M92<L)TEM86=E0V]L)RPG861D)RP@.,'@.P-# Y+" G17AT0V]L)R -"F5X96,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L92<L)U1E>'1#;VPG+"=A9&0G
M( T*97AE8R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G86-T:79A
M=&4G(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN#0IE>&5C('-P
M7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G+"=S=&%R=%]F=6QL)PT*
M9V\-"BTM(%=A:70@.9F]R($94($EN9&5X:6YG('1O(&-O;7!L971E(&%N9"!C
M:&5C:R!.5"]7:6XR2R!!<'!L:6-A=&EO;B!L;V<@.9F]R('-U8V-E<W,O97)R
M;W)S+BX-"@.T*#0IS96QE8W0@.*B!F<F]M($944U1A8FQE#0IG;PT*+2T@.4V5A
M<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN($A434P@.9FEL92XN#0IS96QE
M8W0@.2V5Y0V]L+"!);6%G94-O;" @.9G)O;2!&5%-486)L92!W:&5R92!C;VYT
M86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E<B!B>2!+97E#;VP@.
M#0IG;PT*+2T@.4V5A<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN("Y$3T,@.
M9FEL92XN+@.T*<V5L96-T($ME>4-O;"P@.26UA9V5#;VP@.9G)O;2!&5%-486)L
M92!W:&5R92!C;VYT86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E
M<B!B>2!+97E#;VP@.#0IG;PT*#0H-"BTM($-O;F9I<FT@.1E0@.4')O<&5R=&EE
M<RXN+@.T*=7-E('!U8G,-"F=O#0IS<%]H96QP7V9U;&QT97AT7V-A=&%L;V=S
M("=&5%-#871A;&]G)R -"F=O#0IS<%]H96QP7V9U;&QT97AT7W1A8FQE<R G
M1E130V%T86QO9R<@.( T*9V\-"G-P7VAE;'!?9G5L;'1E>'1?8V]L=6UN<R G
M1E135&%B;&4G#0IG;PT*4T5,14-4(&9U;&QT97AT8V%T86QO9W!R;W!E<G1Y
M*"=&5%-#871A;&]G)RP@.)U!O<'5L871E4W1A='5S)RD@.#0IG;PT*#0HM+2!2
M96UO=F4@.1E0@.26YD97AE<R F($-A=&%L;V<@.)B!T86)L92XN#0H-"F5X96,@.
M<W!?9G5L;'1E>'1?=&%B;&4@.)T944U1A8FQE)RPG9')O<"<-"@.T*97AE8R!S
M<%]F=6QL=&5X=%]#871A;&]G("=&5%-#871A;&]G)RPG9')O<"<-"@.T*9')O
4<"!T86)L92!&5%-486)L90T*#0H`
`
end

Full Text Search with a Parameter

I have a Full Text query that works fine when structured as follows:

SELECT First_Name, Middle_Name, Last_Name, Hire_Date, City, Department, Phone_Ext, title, Fax, Secretary_Name, Attorney_Name, Secy_Ext, Home_Phone, Desk_Location, Law_School, Undergraduate_School, Languages, E_mail, CMS_ID, WEB_ID, Notary

FROM dbo.PhotoDir

WHERE CONTAINS (*, 'Jones')

ORDER BY Last_Name, First_Name

However, I would like to replace "Jones" with a parameter, @.LName for example. However, I can't figure out the syntax. I tried the following:

WHERE CONTAINS (*, @.LName)

but received the following error:

The @.LName SQL construct or statement is not supported.

Is there a way to put a parameter in this type of query?

Thanks in advance.

Hi There,

Have a look at this article, mnight give you a clue on what you doing there.

http://technet.microsoft.com/en-us/library/ms187787.aspx

Hope it helps!

|||

Hi lwhalen618,

See this example in sql2005 online book:

USE AdventureWorks;GODECLARE @.SearchWord nvarchar(30)SET @.SearchWord = N'Performance'SELECT Description FROM Production.ProductDescription WHERE CONTAINS(Description, @.SearchWord);GO

See, it's totally fine to use parameter within full-text-indexing. Your code seems okay to me. Please check if you have declared @.LName in your code.

Hope my suggestione helps

|||

I was able to get my code to work using a stored procedure. The query builder within VS2005 would not let me use the additional code before the Select statement.

Thanks for your help.

full text search query

select pID,pcode from MyProduct where freetext(PIndex, 'the')
I have the above full text search query. It generate the following err msg:
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words. "
if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
also generate the same err msg.
I have create a full text search index on product table, catalog, full
population . It will not generate err when i use normal word e.g. 'buy',
'model' etc
I am using MS SQL server 2000 on server 2003. Anything to do with
configuration? Any one can help me with it?'the' a what is termed a noise word so is extracted from the query.
You can get and modify the noise word files the full-text engine uses - do a
search for noise.*, there should be a noise.enu - depends on your locale
though.
Its classed a noise word along with the other symbols because thats not
really what full-text is about, think of all the occurances of 'the' and 'a'
and 'and' for instance - does it really make sense indexing those items?
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> select pID,pcode from MyProduct where freetext(PIndex, 'the')
> I have the above full text search query. It generate the following err
> msg:
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words. "
> if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> also generate the same err msg.
> I have create a full text search index on product table, catalog, full
> population . It will not generate err when i use normal word e.g. 'buy',
> 'model' etc
> I am using MS SQL server 2000 on server 2003. Anything to do with
> configuration? Any one can help me with it?|||the point is that SQL server 2k return as an error/msg. Assume a user
performing a full text search query from coldfusion, user can enter
anything. If user enter the word 'the' and because of SQL server 2k issue an
error/msg, my SQL server service get HANG. I need it manually restart my
service to get my sql server working.
--
xxx
"Tony Rogerson" wrote:
> 'the' a what is termed a noise word so is extracted from the query.
> You can get and modify the noise word files the full-text engine uses - do a
> search for noise.*, there should be a noise.enu - depends on your locale
> though.
> Its classed a noise word along with the other symbols because thats not
> really what full-text is about, think of all the occurances of 'the' and 'a'
> and 'and' for instance - does it really make sense indexing those items?
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "eslim" <eslim@.discussions.microsoft.com> wrote in message
> news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> > select pID,pcode from MyProduct where freetext(PIndex, 'the')
> >
> > I have the above full text search query. It generate the following err
> > msg:
> > "Server: Msg 7619, Level 16, State 1, Line 1
> > Execution of a full-text operation failed. A clause of the query contained
> > only ignored words. "
> >
> > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> > also generate the same err msg.
> >
> > I have create a full text search index on product table, catalog, full
> > population . It will not generate err when i use normal word e.g. 'buy',
> > 'model' etc
> >
> > I am using MS SQL server 2000 on server 2003. Anything to do with
> > configuration? Any one can help me with it?
>
>|||When you implement a search using any search engine you need to validate the
user input, do that using a parser that extracts the noise words from the
users input and then if there is nothing left then give the user a message.
Check this: http://www.aspfaq.com/show.asp?id=2502
Also, you should do error handling in the app to pick up any error - check
for that particular error number and gracefully give the user a help
message.
Getting this message does not hang SQL Server, i've done a lot with ASP,
ASP.NET and Full-Text, its how i do my own search on
http://sqlserverfaq.com, if your SQL Server is hanging you have other more
serious problems - check the SQL errorlog for one, is the server responsive
through Query Analyser?
Is it just the application not behaviouring correctly because its got an
error? What happens if you have another failure, server unavailable - you
need to handle those situations as well.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:2D4AE282-AC3D-416C-8405-4444C5ED01E8@.microsoft.com...
> the point is that SQL server 2k return as an error/msg. Assume a user
> performing a full text search query from coldfusion, user can enter
> anything. If user enter the word 'the' and because of SQL server 2k issue
> an
> error/msg, my SQL server service get HANG. I need it manually restart my
> service to get my sql server working.
> --
> xxx
>
> "Tony Rogerson" wrote:
>> 'the' a what is termed a noise word so is extracted from the query.
>> You can get and modify the noise word files the full-text engine uses -
>> do a
>> search for noise.*, there should be a noise.enu - depends on your locale
>> though.
>> Its classed a noise word along with the other symbols because thats not
>> really what full-text is about, think of all the occurances of 'the' and
>> 'a'
>> and 'and' for instance - does it really make sense indexing those items?
>> Tony.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlserverfaq.com - free video tutorials
>>
>> "eslim" <eslim@.discussions.microsoft.com> wrote in message
>> news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
>> > select pID,pcode from MyProduct where freetext(PIndex, 'the')
>> >
>> > I have the above full text search query. It generate the following err
>> > msg:
>> > "Server: Msg 7619, Level 16, State 1, Line 1
>> > Execution of a full-text operation failed. A clause of the query
>> > contained
>> > only ignored words. "
>> >
>> > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc,
>> > it
>> > also generate the same err msg.
>> >
>> > I have create a full text search index on product table, catalog, full
>> > population . It will not generate err when i use normal word e.g.
>> > 'buy',
>> > 'model' etc
>> >
>> > I am using MS SQL server 2000 on server 2003. Anything to do with
>> > configuration? Any one can help me with it?
>>|||If you're getting an error message returned from SQL Server then the
service isn't "hanging"...it's just that your application needs the
proper error-handling code to deal with this situation.
On Sat, 26 Nov 2005 05:58:02 -0800, "eslim"
<eslim@.discussions.microsoft.com> wrote:
>the point is that SQL server 2k return as an error/msg. Assume a user
>performing a full text search query from coldfusion, user can enter
>anything. If user enter the word 'the' and because of SQL server 2k issue an
>error/msg, my SQL server service get HANG. I need it manually restart my
>service to get my sql server working.|||Maybe I should rephrase my question to : How to perform full text search for
symbols(&^<.>? etc) in addition to normal word
-- scenario:
I have a user interface done in Coldfusion that access SQL server 2K store
procedure on server 2003 that perform full text search on a field of a table.
I have remove all content of noise.enu as user can use anything as a search
index.
Why is it that SQL server or SQL Analyser still return err/msg when I use
symbol as search index (e.g. {/}/?/>/</./,/@./!/$):
select pID,pcode from MyProduct where contains(PIndex, '@.')
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words." - error msg from SQL Analyser
At minimun SQL server should return no record. Instead it return err/msg,
that hangs my SQL service when run from Coldfusion and i had to manual
restart my service. Beside doing extra job of parsing user input, any easy
solution? Any ideas '
xxx
"eslim" wrote:
> the point is that SQL server 2k return as an error/msg. Assume a user
> performing a full text search query from coldfusion, user can enter
> anything. If user enter the word 'the' and because of SQL server 2k issue an
> error/msg, my SQL server service get HANG. I need it manually restart my
> service to get my sql server working.
> --
> xxx
>
> "Tony Rogerson" wrote:
> > 'the' a what is termed a noise word so is extracted from the query.
> >
> > You can get and modify the noise word files the full-text engine uses - do a
> > search for noise.*, there should be a noise.enu - depends on your locale
> > though.
> >
> > Its classed a noise word along with the other symbols because thats not
> > really what full-text is about, think of all the occurances of 'the' and 'a'
> > and 'and' for instance - does it really make sense indexing those items?
> >
> > Tony.
> >
> > --
> > Tony Rogerson
> > SQL Server MVP
> > http://sqlserverfaq.com - free video tutorials
> >
> >
> > "eslim" <eslim@.discussions.microsoft.com> wrote in message
> > news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> > > select pID,pcode from MyProduct where freetext(PIndex, 'the')
> > >
> > > I have the above full text search query. It generate the following err
> > > msg:
> > > "Server: Msg 7619, Level 16, State 1, Line 1
> > > Execution of a full-text operation failed. A clause of the query contained
> > > only ignored words. "
> > >
> > > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> > > also generate the same err msg.
> > >
> > > I have create a full text search index on product table, catalog, full
> > > population . It will not generate err when i use normal word e.g. 'buy',
> > > 'model' etc
> > >
> > > I am using MS SQL server 2000 on server 2003. Anything to do with
> > > configuration? Any one can help me with it?
> >
> >
> >|||The proper way is to put error handing in the coldfusion application to trap
any errors, not just this one.
I think you really ought to look at using LIKE '%@.%' for instance, Full-Text
just isn't for doing what you are trying to do.
If you think about what SQL Server is doing, basically it extracts any noise
stuff from your search term leaving what's left as the argument into the
full-text engine, if there is no argument then no search - thats basically
how it works.
Have you looked at FREETEXT instead of CONTAINS?
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:55E4BE89-6C31-4247-B8F8-DE2AA5F1819A@.microsoft.com...
> Maybe I should rephrase my question to : How to perform full text search
> for
> symbols(&^<.>? etc) in addition to normal word
> -- scenario:
> I have a user interface done in Coldfusion that access SQL server 2K
> store
> procedure on server 2003 that perform full text search on a field of a
> table.
> I have remove all content of noise.enu as user can use anything as a
> search
> index.
> Why is it that SQL server or SQL Analyser still return err/msg when I use
> symbol as search index (e.g. {/}/?/>/</./,/@./!/$):
> select pID,pcode from MyProduct where contains(PIndex, '@.')
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words." - error msg from SQL Analyser
> At minimun SQL server should return no record. Instead it return err/msg,
> that hangs my SQL service when run from Coldfusion and i had to manual
> restart my service. Beside doing extra job of parsing user input, any easy
> solution? Any ideas '
> xxx
>
> "eslim" wrote:
>> the point is that SQL server 2k return as an error/msg. Assume a user
>> performing a full text search query from coldfusion, user can enter
>> anything. If user enter the word 'the' and because of SQL server 2k issue
>> an
>> error/msg, my SQL server service get HANG. I need it manually restart my
>> service to get my sql server working.
>> --
>> xxx
>>
>> "Tony Rogerson" wrote:
>> > 'the' a what is termed a noise word so is extracted from the query.
>> >
>> > You can get and modify the noise word files the full-text engine uses -
>> > do a
>> > search for noise.*, there should be a noise.enu - depends on your
>> > locale
>> > though.
>> >
>> > Its classed a noise word along with the other symbols because thats not
>> > really what full-text is about, think of all the occurances of 'the'
>> > and 'a'
>> > and 'and' for instance - does it really make sense indexing those
>> > items?
>> >
>> > Tony.
>> >
>> > --
>> > Tony Rogerson
>> > SQL Server MVP
>> > http://sqlserverfaq.com - free video tutorials
>> >
>> >
>> > "eslim" <eslim@.discussions.microsoft.com> wrote in message
>> > news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
>> > > select pID,pcode from MyProduct where freetext(PIndex, 'the')
>> > >
>> > > I have the above full text search query. It generate the following
>> > > err
>> > > msg:
>> > > "Server: Msg 7619, Level 16, State 1, Line 1
>> > > Execution of a full-text operation failed. A clause of the query
>> > > contained
>> > > only ignored words. "
>> > >
>> > > if i replace the word 'the' with symbol such as '-' or '?' or '>',
>> > > etc, it
>> > > also generate the same err msg.
>> > >
>> > > I have create a full text search index on product table, catalog,
>> > > full
>> > > population . It will not generate err when i use normal word e.g.
>> > > 'buy',
>> > > 'model' etc
>> > >
>> > > I am using MS SQL server 2000 on server 2003. Anything to do with
>> > > configuration? Any one can help me with it?
>> >
>> >
>> >

full text search query

2012年3月26日星期一

Full Text Search Query

Hi,
If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no
problem.
But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ')
returns an error, because the word 'the' is in the black list.
On my website, the users can check a checkbox if they want a search on all
the words they have entered.
Is there a way (something else having my own blacklist and removing by
myself the black words before sending the query to SQL Server...) to avoid
this error ?
If the only way is to use my own blacklist, is there a way to retreive the
SQL Server's one ?
Thanks.Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:

> Hi,
> If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '),
> no
> problem.
> But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse"
> ') returns an error, because the word 'the' is in the black list.
> On my website, the users can check a checkbox if they want a search on all
> the words they have entered.
> Is there a way (something else having my own blacklist and removing by
> myself the black words before sending the query to SQL Server...) to avoid
> this error ?
> If the only way is to use my own blacklist, is there a way to retreive the
> SQL Server's one ?
You can remove the list of words from the SQL Server noise word file, or
remove the words from your query. I ended up clearing out my noise word
file. It depends on your installation path (and possibly SQL Server version)
as to where your noise word files are, mine are in
\MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
noise word file is noise.enu, but I also edited noise.eng. If you decide to
remove all the noise words from the config, do not empty the file
completely - leave a single line with a space on it. You'll have to
repopulate your catalogs if you change your noise word file so that the
words you have removed are added to the catalogs.
BTW: a more appropriate group for this is
microsoft.public.sqlserver.fulltext :)
Dan|||Steph,
In addition to what Daniel says below... Yes, the full path to the noise
word files (noise.enu) is SQL Server version specific, I usually shorten it
to the files under \FTDATA\SQLServer\Config. Note, that noise.enu = US
English, noise.eng = UK English and noise.dat = Neutral and are related to
the column-specific "Language for Word Breaker" in the FT Indexing wizard.
Also, run a Full Population after modifying the noise word files.
You may also want to review KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
For other SQL FTS resources see "SQL Server 2000 Full-Text Search Resources
and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:um7ny3iLFHA.656@.TK2MSFTNGP14.phx.gbl...
> Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
>
'),
all
avoid
the
> You can remove the list of words from the SQL Server noise word file, or
> remove the words from your query. I ended up clearing out my noise word
> file. It depends on your installation path (and possibly SQL Server
version)
> as to where your noise word files are, mine are in
> \MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
> noise word file is noise.enu, but I also edited noise.eng. If you decide
to
> remove all the noise words from the config, do not empty the file
> completely - leave a single line with a space on it. You'll have to
> repopulate your catalogs if you change your noise word file so that the
> words you have removed are added to the catalogs.
> BTW: a more appropriate group for this is
> microsoft.public.sqlserver.fulltext :)
> Dan
>

Full Text search performance tuning.

Hi Everyone,
I have a performance issue with a SQL Server query and i need some
assistance with it. It would be great if you could offer your inputs on
it.
We have a SQL Server database that is of a rather large size [20GB +].
The most significant table "Order_Detail" has a large number of rows [a
few million rows]. We need a query that will retrieve a keyword search
result in the shortest span of time from this table. The resultset we
need is dependent on a few other tables. The query we use has joins on
these tables. These other tables are also of similar sizes.
The problem is that the query takes too long a time to execute to be
practical.Could anyone take a look at the query and let me know how to
optimise it best ?
FTSI is not implemented on the Order_Details table.
The tables - Order, Order_Detail, Order_Tracker are indexed on the
"order_id" column.
-- SQL query --
SELECT o.order_id as "Order ID", o.date AS 'Received date',
od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
'Email Text'
FROM Order o, Order_Details od, Order_Tracker ot, Customer c
WHERE od.order_ID = o.order_ID
AND od.customerID = c.customerID
AND od.poID = ot.poID
AND o.date >= ** X date **
AND o.date <= ** Y date **
AND ot.Email=c.Email
AND ot.Msg like '%<Keyword to be searched for>%'
ORDER BY o.order_ID, o.date, o.customerID
-- End of query --
*Note->The query is modified to give you a sense of what i am trying to
do. It is not the exact same query i use here.
We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
better performance but only marginally. What are your thoughts on using
FTSI when the query will end up using complex joins and large volumes
of data ? If this approach will solve our performance issue, can
someone direct me on how best to do it ?
Our only constraints are that the database structure cannot be
modified. However the SQL query or the approach can be.
Thank you for your inputs in advance!
Regards,
Venky
The short answer is that other than full-text indexing an indexed view in
sql 2005 or partitioning by date there is no easy way to increase your
performance. I suspect you may also have a lazy spool. Could you post the
execution plan here so we can evaluate it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<rv.venky@.gmail.com> wrote in message
news:1159890834.003818.162910@.m73g2000cwd.googlegr oups.com...
> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows [a
> few million rows]. We need a query that will retrieve a keyword search
> result in the shortest span of time from this table. The resultset we
> need is dependent on a few other tables. The query we use has joins on
> these tables. These other tables are also of similar sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying to
> do. It is not the exact same query i use here.
>
> We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
> better performance but only marginally. What are your thoughts on using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky
>
|||Hello Venky
Please post the scripts for your tables. This is essential to work out what
needs to be indexed.
Why are you not joining Order_tracker to Order by OrderId?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs
> on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows
> [a few million rows]. We need a query that will retrieve a keyword
> search result in the shortest span of time from this table. The
> resultset we need is dependent on a few other tables. The query we use
> has joins on these tables. These other tables are also of similar
> sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying
> to do. It is not the exact same query i use here.
> We tried to use FTSI and "CONTAINS" to search for the keyword. It
> gives
> better performance but only marginally. What are your thoughts on
> using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky

2012年3月22日星期四

Full Text Search for Find Methods

Hello,
I have to implement a method FindProduct, which takes a parameter search query and returns product based on the query.

I think i can use full text search here but don't know how. I tried to read a various way but could not figure out.

123 [DataObjectMethodAttribute(DataObjectMethodType.Select,true)]4public DataLayer.shopDBDataSet.ProductsDataTable GetProducts()5 {6return Adapter.GetProducts();7 }89 [DataObjectMethodAttribute(DataObjectMethodType.Insert,true)]10public bool AddProduct(string UPCCode,string Description,string Size)11 {12 shopDBDataSet.ProductsDataTable products =new shopDBDataSet.ProductsDataTable();13 shopDBDataSet.ProductsRow productRow = products.NewProductsRow();1415 productRow.UPCCode = UPCCode;16 productRow.Description = Description;17 productRow.Size = Size;1819 products.AddProductsRow(productRow);20int rowsAffected = Adapter.Update(products);21return rowsAffected == 1;22 }2324 [DataObjectMethodAttribute(DataObjectMethodType.Delete,true)]25public bool DeleteProduct(string UPCCode)26 {27int rowsAffected = Adapter.Delete(UPCCode);28return rowsAffected == 1;29 }3031 [DataObjectMethodAttribute(DataObjectMethodType.Select,false)]32public DataLayer.shopDBDataSet.ProductsDataTable FindProducts(string searchQuery)33 {3435throw new System.NotImplementedException();3637 }38

You can see the method at line number 32. I could not figure out the way to implement it although i know full text search is a good choice.

I have following table adapter

GetProducts(); GetProductsByManufacturerName(); GetProductsByManufacturerID(), and GetProductsByUPCCode();

Product table contains UPCCode, Description, Size columns

But i need to get the product which returns the description containing search query.

I know some of you have done something similar to this.

Could you please guide me...

Thanks in advance

...

The Full Text index require four predicates CONTAINS,CONTAINSTABLE,FREETEXT and FREETEXTTABLE and the Microsoft search service and the catalog must be populated before you can get search results. You can implement it in your application but there is limited support for SQL Server Express. The links below will take you in the right direction if you decide to implement it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms142559.aspx
http://msdn2.microsoft.com/en-us/library/ms142547.aspx

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

|||

Thanks a lot...

i got the full text indexes for my tables.

But do you suggest me to create a productstableadapter that uses FREETEXT SQL query.

or just call it from the function.

Thanks

|||

There are samples provided by Microsoft but I think you should use a stored proc like the samples in my first post and call the stored proc in your ADO.NET code. Hope this helps.

|||

Thank you again...

how do i pass search query entered by user.

SELECT ProductName
FROM Products
WHERE FREETEXT (ProductName, 'spread' )

instead of spread i would like to pass user input.

thanks once again

|||

Then the CONTAINS predicate is more suited for your needs because it can be used in seven or more context when searching, run a search for all four predicates I posted in my first reply in SQL Server BOL(books online). Here are all the uses of CONTAINS. Hope this helps.

A. Using CONTAINS with <simple_term>

B. Using CONTAINS and phrase in <simple_term>

C. Using CONTAINS with <prefix_term>

D. Using CONTAINS and OR with <prefix_term>

E. Using CONTAINS with <proximity_term>

F. Using CONTAINS with <generation_term>

G. Using CONTAINS with <weighted_term>

H. Using CONTAINS with variables

|||

Hey Caddre (my favorite poster)

I want to piggy back on this. I implemented the code used in the links above but something still isn't right. Here's my code:

<code>

Sub search(ByVal sender As Object, ByVal e As System.EventArgs)
GridView1.Visible = True
Dim DBCOnnection As SqlConnection
Dim Criterion As String = Request.QueryString("Criterion")
DBCOnnection = New SqlConnection("Data Source=server/db;Initial Catalog=SOS_KnowledgeBase;Integrated Security=True")
DBCOnnection.Open()

Dim strSQL As String
Dim objDataset As New DataSet()
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()

strSQL = "exec dbo.sp_search @.Application, @.Category, @.textsearch"

objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, DBCOnnection)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Application", DDLApp.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Category", DDLCat.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.textsearch", textsearch.Text)
objAdapter.Fill(objDataset)
Dim oView As New DataView(objDataset.Tables(0))
GridView1.DataSource = oView
GridView1.DataBind()

DBCOnnection.Close()
If GridView1.PageCount <= 0 Then
Label1.Text = "No Results Matched Your Query."
Else

If GridView1.PageCount > 0 Then
Label1.Text = ""
End If

End If
Button1.Visible = False
Button2.Visible = True

End Sub

</code>

<html>

<asp:content id="Content1" contentplaceholderid="headertext" runat="server">

<asp:DropDownList ID="DDLApp" runat="server" DataTextField="Application" DataValueField="Application" AutoPostBack="True" DataSourceID="Application" AppendDataBoundItems="True" OnSelectedIndexChanged="refresh">
<asp:ListItem Value="0">-- Choose an Application --</asp:ListItem>
</asp:DropDownList><br />

<asp:SqlDataSource ID="Application" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Application.Application, Article.ApplicationID FROM Application INNER JOIN Article ON Application.ApplicationID = Article.ApplicationID">
</asp:SqlDataSource>
<asp:DropDownList ID="DDLCat" runat="server" DataTextField="Category"
DataValueField="Category" DataSourceID="Category" AppendDataBoundItems="True">
<asp:ListItem Value="0" Selected="True">-- Select All --</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="Category" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Category.Category FROM Category INNER JOIN Article ON Category.CategoryID = Article.CategoryID INNER JOIN Application ON Article.ApplicationID = Application.ApplicationID WHERE (Application.Application = ISNULL(@.Application, Application.Application)) ORDER BY Category.Category">
<SelectParameters>
<asp:ControlParameter ControlID="DDLApp" Name="Application" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

<asp:Label ID="textsearch" runat="server" CssClass="box-title" Text="Keyword(s)"></asp:Label><br />
<asp:TextBox ID="searchbox" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="search" Text="Search Now" Width="83px" />

</asp:content>

</html>

CREATE PROCEDURE [sp_search]

@.Application nvarchar (25),
@.Category nvarchar(25),
@.textsearch nvarchar(100)

Begin

If @.Category ='-- Select All --'
SELECT Article.ArticleID, Application.Application, Category.Category,
Article.Title, Article.DateUpdated AS [Last Updated]
FROM Article INNER JOIN Application ON
Article.ApplicationID = Application.ApplicationID
INNER JOIN Category ON Article.CategoryID = Category.CategoryID
WHERE Application.Application = ISNULL(@.Application, Application.Application) AND
Category.Category = ISNULL(null, Category.Category) AND
Article.Title IN (
SELECT Title
From Article
WHERE (CONTAINS(*, @.textsearch)))

If @.@.RowCount=0
--raiserror ("No Results.", 16, 1)
RETURN @.@.Error
End
GO

</stored proc>

Here's the error I keep getting:

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=13
Message="Syntax error occurred near '('. Expected '' in search condition 'Keyword(s)'."
Number=7631
Procedure="sp_search"
Server="my server"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.ProviderBase.DataReaderContainer.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ASP.search_aspx.search(Object sender, EventArgs e) in D:\Documents and Settings\uid\My Documents\Visual Studio 2005\WebSites\SOSKnowledgeBase\search.aspx:line 33
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Can you tell me what I am doing wrong here? Thanks.

Cordell

|||

Never mind...so stupid. I was referencing a label instead of the actual textbox!!!!!

Now that this is fixed, does anyone know a link or two that shows me how to strip commas, periods, etc from textbox entries before you pass it to a procedure?

Cordell

|||

I think you are calling Contains too late in your query in the WHERE clause of a JOIN which is just a filter and if you are in Express there is limited support for Express. You should check out the other three predicates especially the tables version. Hope this helps.

FreetextTable

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

Containstable

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

And thanks for the compliment.

Full Text Search Filters What is installed?

Is there a way to query and identify which file extensions have a Full Text
Search filter installed for SQL2005?
Thanks
TomLook up sys.fulltext_document_types catalog view in Books Online.
select *
from sys.fulltext_document_types
ML
http://milambda.blogspot.com/|||that only gives the iFilters which ship with SQL Server 2005. If you choose
to load the ones via
exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
go
you need to get a complete list via filtreg
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3388D19A-B26B-470A-9E08-497B93987A26@.microsoft.com...
> Look up sys.fulltext_document_types catalog view in Books Online.
> select *
> from sys.fulltext_document_types
>
> ML
> --
> http://milambda.blogspot.com/|||Good to know, thanks.
ML
http://milambda.blogspot.com/|||Hi ML,
I got the error when run the SQL:
Invalid object name 'sys.fulltext_document_types'
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3A88C201-4D67-4540-A064-23B553D2D932@.microsoft.com...
> Good to know, thanks.
>
> ML
> --
> http://milambda.blogspot.com/|||Are you trying this on SQL 2005?
ML
http://milambda.blogspot.com/

Full text search can not return result

Hi all,
In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
From http://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Very rarely when you are in the middle of a merge you may get an
inaccurate count like what you are seeing. it lasts for milli-
seconds.
On Jan 16, 4:56 am, ken<nos...@.developmentnow.com> wrote:
> Hi all,
> In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
> Fromhttp://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com

full text search and verbs forms

Hi,
I'm running win 2003 and sql 2000 sp3a in spanish.
When I search in a full text query for a verb (using contains and not an
inflectional search) the result include the correct verb form but also
the various forms of that verb . For example if I search "comprar" the
results include "comprar".
I have the same problem if I search for a phrase containing verbs.
Is there a way to solve this problem?
Thanks in advance,
Robert.
Robert,
Could you post the actual CONTAINS query you are using? Specifically, are
you using a trailing asterisk "*" wildcard in your query?
Also, you should know that SQL 2000 FTS is accent insensitive regardless of
the database or table collation.
Regards,
John
"Robert" <rbroggi@.seciu.edu.uy> wrote in message
news:40A0FFDA.1090404@.seciu.edu.uy...
> Hi,
> I'm running win 2003 and sql 2000 sp3a in spanish.
> When I search in a full text query for a verb (using contains and not an
> inflectional search) the result include the correct verb form but also
> the various forms of that verb . For example if I search "comprar" the
> results include "comprar".
> I have the same problem if I search for a phrase containing verbs.
> Is there a way to solve this problem?
> Thanks in advance,
> Robert.
>
|||Hi John,
The query is: SELECT * FROM Textos WHERE CONTAINS(Texto,'"comprar
proximamente"')
The result include results like "comprar proximamente".
What do you mean by "SQL 2000 FTS is accent insensitive" ? I thought
that the full text engine was accent sensitive. For example if you
search "debera" will not be the same as "deberia". Is this correct?
Thanks for the help,
Robert.
John Kane wrote:
> Robert,
> Could you post the actual CONTAINS query you are using? Specifically, are
> you using a trailing asterisk "*" wildcard in your query?
> Also, you should know that SQL 2000 FTS is accent insensitive regardless of
> the database or table collation.
> Regards,
> John
>
> "Robert" <rbroggi@.seciu.edu.uy> wrote in message
> news:40A0FFDA.1090404@.seciu.edu.uy...
>
>

full text search : Search orders of single input of "multiple word

Sql server express advanced edition
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:

> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada

Full Text Search (2005) - How to determine word offset in CONTAINS query?

Does anyone know if it is possible to determine the relative word offset (the Occ) from a simple-term query such as:-

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "mountain biking" ');

So, given the text:-

"Maybe it's just because I'm new to mountain biking, but I had a terrible time getting used to these pedals."

I would like the query would return both the text and the word offset of 8. To me, it seems like this would be quite useful as I want to highlight the found text for the user to see. Obviously I can do a post-SELECT scan of the string to find the values but this would seem unnecessary.

If anyone can give me any pointers I'd be very grateful.

Thanks

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93 since this is not a SQL Server Data Mining feature.|||I think I will, but I wasn't sure where to post the question as there isn't an FTS forum (yet!). Thanks for your suggestion.sql

订阅：博文 (Atom)

2012年3月29日星期四

Fulldatabase backup query

Fulldatabase backup query

Fulldatabase backup query

full text soundex problem

Full text slow startup on SQL 2005 x64, Win2003 x64

2012年3月27日星期二

Full Text Search without any result

Full Text Search with a Parameter

full text search query

full text search query

full text search query

2012年3月26日星期一

Full Text Search Query

Full Text search performance tuning.

2012年3月22日星期四

Full Text Search for Find Methods

Full Text Search Filters What is installed?

Full text search can not return result

full text search and verbs forms

full text search : Search orders of single input of "multiple word

Full Text Search (2005) - How to determine word offset in CONTAINS query?

Getting error MS SQL2000

Blog Archive

About Me