SQL Server Search Forum

Presence of ESCAPE option in a LIKE clause changes results

Fri, 13 Nov 2009 18:39:54 Z

Running SQL Server 2008 SP1 on Windows 7 or Windows 2003 R2 SP2 32-bit OS.

With Japanese_CI_AI as the collation, put two values into a varchar column called TESTCHAR of a table called TESTTABLE:
シヤウTest1
シャクTest2

Issue a query:
select TESTCHAR from TESTTABLE where TESTCHAR like 'シヤ' ESCAPE '\'

One row is returned.

Issues a second query without the ESCAPE clause:
select TESTCHAR from TESTTABLE where TESTCHAR like 'シヤ'

Two rows are returned.

One basic question is should the Japanese collation ignore the big/small Katakana character differences?

The second question is why would the simple presence of the escape option being specified change the results? I tried different escape characters, including '#', 'a' and a Japanese character, and in all cases only one row was returned.

Full text indexing rank inconsistent with FREETEXTTABLE

Sun, 22 Nov 2009 02:24:30 Z

Hi,

I've encountered a problem with my newly setup FTI in MSSQL Express 2005.

I run a rebuild on the catalog like so: ALTER FULLTEXT CATALOG productindexcatalog REBUILD

I then run the following query against the table:

SELECT KEY_TBL.[RANK], FT_TBL.ProductName, FT_TBL.ProductID, FT_TBL.Url, FT_TBL.ProductPriceIncVat
               FROM ProductIndex AS FT_TBL
               INNER JOIN
               FREETEXTTABLE(ProductIndex, ProductName, 'Aficio SP C220N') AS KEY_TBL
               ON FT_TBL.ProductID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.[RANK] DESC

The result is 3 rows with ranks; 731, 365 and 365 respectively and as expected. If I then change the query to just be "Aficio" I get one result with a rank of 240 (as expected again). If I then run the first query again I get ranks 240, 0 and 0. The table hasn't changed in this time and I can't work out why this would be.

Does anyone have any suggestions?

Thanks,
Colin.

Full-text search within few tables

Mon, 23 Nov 2009 11:29:20 Z

Hi,

I am trying to perform full-text search on tableset which can look like this:

Person (Id, Firstname^, Lastname^, AddressId)

Address(Id, Street^, City^)

Columns with '^' are full-text indexed. Person.AddressId is FK to Address.Id.

The goal is to get resultset of person IDs for query like: find( "Peter NYC" or "Chicago Kate") - Meaning: "Find all Peters who live in NYC or Kates from Chicago".

How add to the followin query searching whithin Address table?

 select [Key], Rank
 from freetexttable(Person,(Firstname,Lastname), "Peter NYC" )

I know that I can create View with all this data in one object, but maybe there is better solution?

Google and bing don't want to show me any useful results :/

Thanks in advance for your time.

Cheers

*) topic moved from sql engine section

Comparing versions of databases

Thu, 19 Nov 2009 19:52:42 Z

I have two databases. One on server A and the other on server B. Version A has been modified and properties changed like adding a new field to a table or changing a datatype from nchar to nvarchar. Is there something that is already built into SQL server where I can compare versions A and B to see the difference?

DBA

SQL Server: Full text index for varchar(max)

Wed, 18 Nov 2009 04:51:50 Z

Hi,
I have varchar(max) type column in a table where I want to create fulltext index for this column to search better. First I tried creating index for this column, but it did allow me to do so. So I try to create fulltext index. Please tell me how to achieve this for faster search for this column
Thanks

How to detect if full text indexing feature is installed in SQL Server 2008

Wed, 18 Nov 2009 11:57:16 Z

hi everyone,
i installed sql server 2008. One of my developer wants full text indexing feature to be installed in the server. i think i installed all the features. i want to know the full text indexing is installed in the system and i want to know how to install the feature in the existing sql sever which is installed in the server machine(64-bit) and sql server 2008 (64-bit). consider me as beginner for SQL server 2008

Full Text Search with LINQ in SQL Server 2008

Mon, 16 Nov 2009 07:03:05 Z

Hi Experts,

Does anyone know about "Full Text Search" ? Any sample code or tutorials would be greatly appreciated.

Currently, I am using LINQ with my application. Can I implement "Full Text Search" with LINQ ?

Note : I am using SQL Server 2008.

Thanks.

"Knowledge Shared Is Knowledge Gained"

Full-text search with wildcard won't work with stoplist

Wed, 11 Nov 2009 01:27:41 Z

When executing the following full-text query using a wildcard prefix search, no records are returned when using the default system stoplist. When I use an empty stoplist, the wildcard prefix search returns the record I am looking for.

SELECT *
FROM tblSQLFULLTEXT
WHERE CONTAINS((sentence), N'"A-57655*"', LANGUAGE 1033)

-- returns 0 rows when using the default system stoplist
-- returns "The part number is A-57655 and the color is brown" using an empty stoplist

Using an exact query returns the record when using the default system stoplist:

SELECT *
FROM tblSQLFULLTEXT
WHERE CONTAINS((sentence), N'A-57655', LANGUAGE 1033)

Could the problem be the dash? Sys.dm_fts_parser with the wildcard string returns the same results using either stoplist.

Use Full-Text Search to find out similar terms

Mon, 16 Nov 2009 07:33:30 Z

Let's say there's a database table called ListedURLs, which has a column [url], containing ten thousands of listed URLs. We're studying how to develop a searching program that if an user search for http://iyp.com.hk, similar URLs in the ListedURLs table will be returned, and results are ordered by similarity. Can it be done by SQL Server 2000 Full-Text Search? How to do it?

Update database table in user Defined function

Wed, 11 Nov 2009 06:50:27 Z

i am using sql 2005
my requirement is, i want to use Update in user Defined function and that user defined function will be used in other Update query assigning value to a column.

currently we are using scalar function but it is neither allowing update function in it nor couldcall any stored procedure that could on it part could update the database table.
waht need to be done?

web developer

FT Search exposed to user

Mon, 09 Nov 2009 21:37:28 Z

I would like to be able to use a CONTAINS clause to do a search for words in a column. However I would like to let my users specify the search terms without having to put AND between their words.

I know that FREETEXT will accept the words without the AND but then they can't use wildcards and they will get a large number of near matches (which is not what I want).

Any sugestions?

Jacob

Using a Stored Procedure with a contains statement and wildcards

Tue, 03 Nov 2009 17:24:56 Z

<br/>(<br/>@NounSearch nvarchar(60) = '""',

@DescriptionSearch nvarchar(60)= '""',

	

)

AS

	SET NOCOUNT ON;

	

SELECT     TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 

                      p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 

                      p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,

		      i_thumb.item_pik as Expr2, (ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,

                      i_pdf.item_pik AS Expr3,(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url

FROM         pricesvc AS p LEFT OUTER JOIN

                      mfr AS m ON m.mfr_pik = p.mfr_pik 

                      LEFT OUTER JOIN

                      image AS i_image ON i_image.item_pik = p.item_pik AND i_image.image_basepath_pik =1020

                      LEFT OUTER JOIN

                      image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik

                      LEFT OUTER JOIN

                      image AS i_thumb ON i_thumb.item_pik = p.item_pik AND i_thumb.image_basepath_pik =1040

                      LEFT OUTER JOIN

                      image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik

                      LEFT OUTER JOIN

                      image AS i_pdf ON i_pdf.item_pik = p.item_pik AND i_pdf.image_basepath_pik =1021

                      LEFT OUTER JOIN

                      image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik

WHERE   (CONTAINS (p.product_name,  @NounSearch) AND CONTAINS (p.*, @DescriptionSearch))

an example of what I am passing into the stored procedure is
@NounSearch = "Valve"
@DescriptionSearch = "1/2" AND "Inch"

That works PERFECT! Now for the problem. If I want to pass a null value through one of these parameters, I don't get back any results. What is the best way to pass in a null value as a wildcard? or the other way around what is the way of passing a wildcard in instead of a null value.
IE.

@NounSearch = "NULL"
@DescriptionSearch = "1/2" AND "Inch"

OR

@NounSearch = "**%%" <-- wildcards haha
@DescriptionSearch = "1/2" AND "Inch"

Harley

Havin troubles with freetext (didn't get an answer searchin overhere)

Thu, 05 Nov 2009 13:38:38 Z

Hi i have a simple question...

What i need to fix to use freetext when i using cyrrilic lang database ?!
(but all my tables contains only english words)

1. I made fts catalog with english language
2. I made fts index in my table ...
3. I wrote the query which contains : select * from dbo.table where freetext (*, 'X')
4. runned the query an i didnt get any rows but all my rows contains the word Xilinx in the second column...

Im using russian localised sql server ...

think i got a problem with some local settings but every time i change something in my database and table (i even add table with russian words only with freetext index ) it still doesnt work

thank you for your help with it !

Fulltext auto tracking not working

Mon, 02 Nov 2009 09:26:19 Z

I have a problem getting auto tracking to work on a specific server. My database structure looks like this:

CREATE TABLE [dbo].[Article](

	[ArticleId] [int] IDENTITY(1,1) NOT NULL,

	[Text] [nvarchar](max) NOT NULL,

	CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 

	(

		[ArticleId] ASC

	) 

	WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO



CREATE FULLTEXT INDEX ON [dbo].[Article]

(

	[Text] LANGUAGE [English]

)

KEY INDEX [PK_Article] ON [MyCatalog]

WITH CHANGE_TRACKING AUTO

GO

When I run the following code:

INSERT INTO [Article]([Text]) VALUES('Test text1')



SELECT 

	[ArticleId],

	[Text]

FROM [dbo].[Article]

WHERE CONTAINS([Text], '"text1"')

...I would expect to find the row I just inserted. And again if I run the code:

UPDATE [Article] SET [Text]='Test text2'



SELECT 

	[ArticleId],

	[Text]

FROM [dbo].[Article]

WHERE CONTAINS([Text], '"text2"')

...I would expect to find the same row. This all works fine on my development machine, but not on a another specific server. Even a full population of the catalog doesn't help i the second case, I have to rebuild the catalog and then do a full population to find the expected row.

Any ideas on how to troubleshoot this is very much appreciated.

Best regards

Hendrik W. Hansen

SQLEXPRESS 2009 FULL TEXT SEARCH

Thu, 15 Oct 2009 18:46:14 Z

The first 1 or 2 times that I try to do a full text search (using CONTAINS(...)), I get the following error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.

After that it starts working ok. I'm working from within Visual Studio 2008 and using SQLEXPRESS 2009 with advanced services.

JLRosado

Searching based on wildcard character

Fri, 30 Oct 2009 02:54:09 Z

Hi, I would like to search a text based on a series of wildcard characters. For instance, if the user inputs the below search sequence, they should get the following outcome:

Input: K*n
Output: Ken

Input: *e*
Output: Ken, Gerald

I understand that by using full-text search's "contain" keyword, we can utilize the wildcard (*) character but the * has to be placed behind the search keyword. Therefore, I was wondering if the above is achievable. Thanks in advance.

SQL Server 2008 freetexttable on phrase words

Wed, 28 Oct 2009 15:53:51 Z

I read the related topics to my question but none of them would provide any results even though my issue is similar with the ones described.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a20ddb82-c3e7-4e1f-91b9-f865f9d2a6c5/
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/42409ada-9535-4920-aebf-6e583df689e8

In sql 2005 when I search for "traffic room meeting" (double quotation) will return nothing.

In sql 2008 searching for the same "traffic room meeting" will return 177 rows that do not contain the search phrase as is at all, even more than that, some of the results do not contain any word as typed but just words like "meet".
Even more puzzling, if I search for "traffic room" I will get 150 rows, less than the more complicated phrase "traffic room meeting". For sure it looks like somehow even though I want to search the phrase enclosed under double quotation, SQL Server 2008 is doing a search like: traffic or room or meeting

Is this a bug or do I miss some settings ? I know about the new changes that were implemented in 2008, specifically at this link but even so, the behavior is not consistent with the specification:
http://technet.microsoft.com/en-us/library/ms143709.aspx

Feature	Scenario	SQL Server 2005	SQL Server 2008
FREETEXT or FREETEXTTABLE	Searching for a exact phrase.	If the search string (freetext_string) is enclosed within double quotation marks, stemming and thesaurus matches are not performed, and the string returns only exact matches as if it were a phrase search. For example, specifying "fast ships" in a FREETEXT predicate would return only rows that contained "fast ships". The phase "ship your package fast" would not be returned.	Phrase searches are no longer allowed by FREETEXT and FREETEXTTABLE, which perform stemming and thesaurus matches regardless of whether single or double quotation marks enclose the search string. For example, searching on "fast ships" would return both "fast ships" and "ship your package fast".

Contains with wildcard iqnores special character

Tue, 20 Oct 2009 13:13:08 Z

Having fts catalog with accent sensitive = true and language on column is set to swedish. A test table contains the following values:

bjornfot
uk bjorn
björnfot
uk björn

then when i run this query:

select

* from SearchWords where contains(*,N'"*björn*"')

results:
uk björn

which should be:
björnfot
uk björn

then when running:

select

* from SearchWords where contains(*,N'"*bjorn*"')

results:
bjornfot
uk bjorn
björnfot
uk björn

which it should be:
bjornfot
uk bjorn

Any idrea of why the results are so?

thanks in advance!

sql server error

Sun, 25 Oct 2009 17:49:56 Z

Getting this error when I turn on my PC - any advice ?

your sql server instulation is either corrupt or has been tampered with (could not open sqlevn70.rll) please uninstall then re-run setup to correct this problem

IFTS Master merge keep failing

Tue, 06 Oct 2009 12:20:28 Z

Hi,
I'm using IFTS of sql server 2008.
I'm indexing html documents comperssed with Zip Ifilter (of IFilter Shop).
And I have several issues, I currently can't resolve.
Currently the catalog has about 500,000 documents, the popultaion takes sevral hours (between 5-10), when the fdhost at start (about first 300,000) documents is on 15-70% CPU usage and after that it's on 0%, and the remaining rebuold process takes a lot of time.
The second issue is that the ctalog is very defragmented sys.fulltext_index_fragments returns about 250 fragments.
When I try to reorganize I receive the following error:
Msg 30031, Level 17, State 100, Line 1
A full-text master merge failed on full-text catalog 'ContentCatalog' in database 'NogaClass' with error 0x80FC755D.

I tried to delete the full-text index and to rebuild it and then reorganize, but it didn't help.

Can anyone please advice?

P.S. The defragmentation causes a very slow search performance.

Tips for improving FTS performance?

Mon, 19 Oct 2009 15:23:08 Z

I have a table with about 5 million records and am trying to do a simple full-text search on one of its columns. The query looks something like this:

SELECT COUNT(*) FROM tDay15 WITH (NOLOCK) WHERE CONTAINS([Segment], '"google"')

This is taking several seconds to complete and I'm wondering if anyone can give me any pointers on how I might improve that. The table and catalog definitions are below:

CREATE FULLTEXT CATALOG [Day15Catalog]WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDay15](
	[OpinionID] [bigint] NOT NULL,
	[Segment] [nvarchar](500) NULL,
	[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_tDay15] PRIMARY KEY CLUSTERED 
(
	[OpinionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ixnc_OpinionID] ON [dbo].[tDay15] 
(
	[OpinionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [dbo].[tDay15](
[Segment] LANGUAGE [English])
KEY INDEX [PK_tDay15]ON ([Day15Catalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
GO

Spanish thesaurus file not loading on Sql Server 2008

Tue, 13 Oct 2009 16:20:28 Z

Hi!

I'm having some problems making full text index search work with a spanish thesaurus file.

Details:

- SQl Server Express 2008 with advanced services on a windows vista box.

- sp_configure 'default full-text language' returns 3082 which is the LCID for Spanish.

- Thesaurus file is located at SQLServerInstallationFolder\MSSQL10.SQLEXPRESS\MSSQL\FTData\tspan.xml and looks like:

- Database and full-text search where restarted engine after each change.

- Thesaurus file is loaded this way:

EXEC sys.sp_fulltext_load_thesaurus_file 3082;

- Thesaurus file is located at SQLServerInstallationFolder\MSSQL10.SQLEXPRESS\MSSQL\FTData\tspan.xml and looks like:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
	<diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Internet Explorer</sub>
            <sub>IE</sub>
            <sub>IE5</sub>
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            <sub>Windows 2000</sub>
        </replacement>
        <expansion>
            <sub>run</sub>
            <sub>jog</sub>
        </expansion>
    </thesaurus>
</XML>

- No error was found inside the log events

Problem:

The following query should return more than one row but only retrieves one:

SELECT * FROM tBrower WHERE CONTAINS(description,'FORMSOF(Thesaurus,"IE5")')

I looked for the spanish registry key under: HKLM\Software\Microsoft\Microsoft SQL Server\YourInstance\MSSearch\Language\span but was not able to find it.

Maybe this is part of the problem.

Thanks a lot!

Full-text using 'contains'

Thu, 15 Oct 2009 23:07:03 Z

Suppose I have a table, under full-text indexing, with column data containing values 'law','lawson','byelaw'. I do a select using CONTAINS '"*law*"'. The first two records are returned but not the one with 'byelaw'.
I suspect the leading '*' is ignored when using 'contains'. Is this by design? I find this hard to believe.
Any thoughts?

Fuzzy search in database

Tue, 13 Oct 2009 13:59:41 Z

Hi all!

I have a task related to fuzzy searching in database.
It is assumed to be an sql server database.
The task is to have an ability to search in large database by approximate input strings.

So, I have a couple of questions to start my research in that task.
Is that the realizable task to implement fuzzy searching in database by myself? Or it can be too tricky?

Or it is more reasonable to use some ready-to-use tools?

Thanks!

('/') forward slash causing trouble me in FullText Search

Thu, 24 Sep 2009 18:59:32 Z

Hello All,

I have a column with fulltext search and it contains 'S/Y' and 'M/Y' special abbreviations.
When I try to use contains function it is not working. I did some researches and I try to remove my, m, s, y nose words from nose file, but problem still continue.

Any Idea how can I fix this issue?

PS:
We are using SQL Server 2005 ( 9.0.3054 ) and Database running on win2k3.
My Databases compatibility level is 90.

Search phrase full text

Tue, 13 Oct 2009 19:13:47 Z

I use the following SQL: select specification from tblProduct where contains(specification, ' "<td>Interfacetype</td><td>: </td><td>Hi-Speed USB" ');

This gives no result. Why does this not work???

full-text search in two columns

Mon, 13 Jul 2009 07:47:23 Z

Hi

Question about full-text search, i have set up a table with two collumns, title and body

I have added a full-text to include both columns.

Now i want to search "google"-style in both columns but i cant get a hit from both at the same querie.

The table include a row: title = abc123, body = xyz987

select title from table where contains((title,body), 'abc123 AND xyz987') is not working

select title from table where contains((title,body), 'abc123) is working and

select title from table where contains((title,body), 'xyz987') is working

How can i get FT to return hits from two different columns?

The columns is nvarchar(MAX) and the server is 2005-sp2

/looc

FullText Not Searching On Phrase With Space Correctly

Fri, 02 Oct 2009 19:51:33 Z

I have a fulltext query that uses the containstable function. The phrase being searched is "over speed". I'm using a case insensitive sort. The result sets are coming back with the word speed but not the word over. The message return states:

The full-text search condition contained noise word(s).

The phrase is not negiotable as I am specifically looking for the phrase "over speed". Is over a noise word?

DJ Baby Anne's Biggest Fan................

Full Text Search Result Ranking Across Multiple but Similar Tables

Wed, 30 Sep 2009 19:45:00 Z

Hello,

I have a very large table that I had to break up into smaller tables and then full-text index it.

Example:

Very Large Table: T1

T1 broken into smaller tables: S1, S2, S3 & S4 where the content of S1+S2+S3+S4=T1

S1-S4 tables all have the same columns i.e. Col1 and Col2.

I have individually created a unique index on Col1 of S1 through S4 tables.

I have individually full-text indexed Col2 of the S1 through S4 tables.

To perform a full-text search query with ranking on the one of these tables I do the following:

SELECT

S1.*,

KEY_TBL.RANK

FROM

INNER JOIN CONTAINSTABLE

(

S1,

N'"test"',

500

) AS KEY_TBL

ON S1.Col1 = KEY_TBL.[KEY]

Order by

KEY_TBL.RANK;

How can I extend the above query to perform the same search on S1 through S4 and rank the result across all the 4 tables?

I do realize that the ranking function in SQL Server (presumably Okapi BM25) logically makes sense if its performed within the context of a single table (not a series of tables). But then, perhaps, there could be an algorithm that performs a relative re-ordering of ranking results across multiple tables (all other variables being the same).

If this cumulative ranking query is not possible, is there a different path I can take (short of reverting back to the T1 table and indexing it).

Thanks,

Tareen

Too many full-text columns or the full-text query is too complex to be executed

Sat, 01 Aug 2009 23:24:27 Z

Hi,
I am using sql server 2005, I am trying to do full text index
I have a table Tbl_Product_with_Index

CREATE TABLE [dbo].[Tbl_Product_With_Index](
    [Product_Id] [int] NOT NULL,
    [Product_Name] [nvarchar](max) NULL,
    [Quantity] [int] NULL,

)

the data in my table look like

Product_Id Product_Name                     Quantity

1	Pro_1	1
2	Pro_2	2
3	Pro_3	3
4	Pro_4	4
5	Pro_5	5
6	Pro_6	6
7	Pro_7	7
8	Pro_8	8
9	Pro_9	9

................

i have 499999 rows in my table
when i run the query

SELECT product_name ,product_Id from Tbl_Product_with_Index
where contains (product_name,'Pro_2')
the result is

Pro_2 2

only 1 row ....

and when i run
SELECT product_name ,product_Id from Tbl_Product_with_Index
where product_name like '%Pro_2%'

i have 111111 rows !!!!!!!!!!!!

beside
when I run
SELECT product_name ,product_Id from Tbl_Product_with_Index
where contains (product_name,'"Pro_*"')

I have => Too many full-text columns or the full-text query is too complex to be executed.

Can any one please tell me why there is much difference between like and contains ?
can contains do the same functionality as Like ?
why i have this error, is there is a bug in sql server

Hope to have an answer

Fulltext problem indexing xml data in SQL2008: 0x80004005 errors and cpu usage near 100%

Thu, 17 Sep 2009 00:28:35 Z

Hi all,

I have been wrestling this for 2 days now and hope that someone can help.

FYI - this has been tested on the following environments:

Microsoft Windows Server 2003 R2 Standard Edition Service Pack 2

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

Windows 7 Enterprise

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

I have a table that stores data in a column of type xml, and a full-text index on it.

The column contains multilingual data and leverages the xml:lang attribute on a per-record basis to define what word breaker to use.

Example:

(Note: the actual xml structure is more complex than the above.)

When I went to populate my table with real data, the log filled up with error messages ("Error '0x80004005' occurred during full-text index population for table or indexed view [...]") and the cpu usage skyrocketed to 98%.

After some digging I was able to identify the culprit - a handful of records having xml:lang="th-TH" (Thai).

The text within the xml is actually English, but the same records previously existed on a SQL2005 server and were indexed without issues.

Also, please note that the xml structure is the same across all records, which are in all sorts of languages.

Now, having recently upgraded to SQL2008 I wondered if anything has changed for the Thai language, but found that it's among those languages that have NOT changed (http://msdn.microsoft.com/en-us/library/ms176076.aspx).

So I played a bit with the xml data to narrow down the scope of the problem and found that 'sometimes' the Thai indexer fails - here is how to reproduce the problem in SQL2008:

1. Create a new table as follows and make [Id] the PK:

create table [TestTable] ([Id] int not null, [Xml] xml null)

2. Create a full-text index on the [Xml] column (don't specify any language; my sql default is English).

3. Add a new record as follows:

insert into [TestTable] values (1, '<Root xml:lang="th-TH">blah</Root>')

-- The above statement does NOT cause any problem: a new row is added, indexed in a split second

-- and if you check the full-text index you'll see Docs Processed = 1 and Pending Changes = 0

-- In fact, if you query sys.fulltext_indexes you'll see that the index has a valid crawl_end_date

4. Now add another record as follows:

insert into [TestTable] values (2, '<Root xml:lang="th-TH"><Title>blah</Title></Root>')

-- The above statement DOES cause the problem: a new row is added, but the indexing process (fdhost)

-- will eat up all your cpu and your log (SQLFTxxxxxyyyyy) will fill with 0x80004005 error messages

-- If you check the full-text index you'll see Fail Count = 1 and Pending Changes = 1

-- Also, if you query sys.fulltext_indexes you'll see that the crawl_end_date for your index is null

If you try the above in SQL2005 you won't have any problems at all.

I wonder if anyone has encountered the same problem and/or can shed some light into this issue.

For now, I'm simply resorting to storing those documents without the xml:lang="th-TH" attribute, but this obviously makes them unsearchable.

Thanks in advance for any help,

Paolo

Arabic full text search

Wed, 23 Sep 2009 10:35:16 Z

My SQL Server 2005 is not supporting Arabic language for Full Text Search. Please can anyone guide me how can I install Arabic language for Full Text Search.

Regards,

Faisal

FullText Index not Active in Table Designer

Tue, 22 Sep 2009 20:31:58 Z

Hi!
I am using sql srvr 2008 exp edition management studio to create FullText index. After creating fulltext catalog, I tried to create fulltext index for a database table from the table designer menu. But the FullText Index is not active and it cannot be selected. What is the possible reason for this? Thanks for your assistance.

Solmek

SQL Sever Full Text Indexing replacement for WRITETEXT/UPDATETEXT

Mon, 21 Sep 2009 20:42:17 Z

We hope to use Full Text Indexing in SQL Server as part of a major project. We want to push many documents into SQL Server generating the full text index as we go. We intend to use the index, but since we are storing the documents in other hardware we do not need to keep the document contents in SQL Server. Using the current releases of SQL Server, we can push the documents into SQL Server and then use UPDATETEXT to set the column containing the doument to "". This removes the document contents (saving space in the DB) and retains the full text index.

Since WriteText and UpdateText will not be supported in future releases, what functions will be available to remove the contents of the full text column, but retain the full text index?

Urgent! SQL 2008 thesaurus file special char åäö

Tue, 15 Sep 2009 11:47:49 Z

hi,
i'm having problem with querying thesaurus file that contains the characters
åäö. the tssve.xml file looks like this:

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
car
truck
van
automobile
båt
</expansion>
<expansion>
Sjuk
forsakringar
Sjukförsäkringar
Sjukvardsförsäkringar
</expansion>
</thesaurus>
</XML>

When i query "car" it return results all rows containing the first 4 sub but
not the last one. I used different soft of querys without working, here is
one of my tests:

declare @Word nvarchar(100)
set @Word = N'car'

select ST.Id, ST.SearchWords, Key_TBL.RANK from SearchTest ST
inner join freetexttable(SearchTest, *, @Word, language 1053) as KEY_TBL
ON ST.Id = KEY_TBL.[KEY]
ORDER By KEY_TBL.RANK DESC
select * from SearchTest where freetext(*,@Word)

Is there a way to solve this problem?

Thanks in advance!

Thesaurus and getting the replacment word

Fri, 18 Sep 2009 07:51:59 Z

Hi

I'm using the thesaurus for our full text search primarily to catch spelling mistakes, is there a way of capturing the substituted word value when I run a FormsOf(Thesaurus query?
i.e. if my thesaurus contains (simplified version):
<replacement>
 <pat>kool</pat>
 <pat>cole</pat>
 cool
 </replacement>

And a user searches for kool, when I display the results for the sub cool, is there a way to pull out the sub so I can add a ' you searched for kool - did you mean cool?'

Thanks

full text search extremely slow

Wed, 26 Aug 2009 12:06:42 Z

i have installed sql server 2005 in windows server 2003 .The full text search was working very fast (less than 1 sec) for about a month and after a server restarting it is working extremely slow(more than 8 min).
I have deleted and recreated the full text index several times but still is very slow.
Please provide me some help

thanks

SQL Server 2008 - Registry keys for Licensed Third-Party Word Breakers

Fri, 17 Apr 2009 13:27:40 Z

It appears that this has NOT been tested under SQL Server 2008.

Version: SQL Server 2008 Developer Edition RTM (MSDN download) + SP1
OS: Windows XP Professional SP3

I'm starting to test SQL Server 2008. We use full-text, in multiple languages. So in SQL Server 2005 we loaded the 6 additional third-party word breakers according to the documentation and everything works fine.

In SQL Server 2008 the third-party word breakers has been reduced to three. So I'm trying to configure them according to the documentation. However, there appears to be major documentation errors in SQL Server 2008.

Issues:

1. The document titled "How to: Load Licensed Third-Party Word Breakers" http://msdn.microsoft.com/en-us/library/ms345188.aspx states that the "FullTextDefaultPath" value is located under
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\MSSQLServer" which is similar to SQL Server 2005.

However I found the entry under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup"

2. The documentation instructs to add a Registry value specifying the path of the "NoiseFile" -- which no longer exist in SQL Server 2008. I checked the language entries for the standard (default) languages under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\MSSearch\Language" and none of those have a "NoiseFile" value.

3. Lastly, the values for the "TsaurusFile" are unqualified. I.e., they only specify the name of the file (without the path). In SQL Server 2005 they were fully qualified. So should they qualified? I'm guessing no, based on the other entries.

So it would appear that the documentation is totally wrong. It certainly hasn't been tested (using the documentation).

Can anyone confirm the correct location of the keys and/or values and what should be entered into them.

BTW, I've already sent feedback on the BOL documentation.

FTS problem with double quotes (")

Wed, 16 Sep 2009 08:45:38 Z

Hi,

I use CONTAINSTABLE query and my target table includes the word 'we"as' (with double quotes ["] in the middle of the word).
I need to find the column containing data for precise matches to my word ('we"as' ).

P.S. I work with Hebrew words and 'we"as' is just example.

Thank in advance,
Maxim

How multipicate in sql server?

Thu, 10 Sep 2009 09:34:20 Z

hello,
How to multiplication in sql server?

its urgent please....