difference between SQL Server 7.0 and SQL Server 2000 full-text search

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by swon » Mon, 15 Jan 2001 07:26:11



I've noticed a difference between SQL Server 7.0 and SQL Server 2000
full-text search.  In a text field that contains:

... <TITLE>Microsoft</TITLE>  ...

SQL Server 7.0 will but SQL Server 2000 will not return a result for the
following queries:

select * from table where contains(text_field, 'Microsoft') or
select * from table where contains(text_field, '"Microsoft"')

My 'default full-text language'  setting is for US english.
exec sp_configure 'default full-text language'
shows
default full-text language          0           2147483647  1033
1033

It appears that language neutral full-text is implicitly turned on for SQL
Server 2000 because adding the <TITLE> start and end tags to the search
string will return a result for SQL Server 2000.

Steve

 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by John Kan » Tue, 16 Jan 2001 03:32:42


Steve,
What OS do you have SQL Server 7.0 as well as your SQL Server 2000 installed
on?

If your SQL 7.0 is installed on NT 4.0, I suspect you are seeing the correct
behavior. However, if your SQL 2000 is installed on Win2K, I suspect you're
seeing the incorrect behavior. If so, you've encountered a recently filed
bug on the Win2K wordbreaker (infosoft.dll). The issues is related to
punctuation that is "right next to", i.e., no space between the letter and
the punctuation. In your case, the < and >. You can revert back to the NT
4.0 behavior, via:
Stopping the "Microsoft Search" service.
Renaming the Win2K infosoft.dll to infosoft.old (or infosoft.w2k, in the
winnt\system32 dir)
Copying over the infosoft.dll from a NT 4.0 Server.
Re-boot the Win2K Server and run a Full Population.

BTW, your SQL Server 2000 is using the default full-text language of 1033
that represents English_US (see SQL 2000 BOL title "default full-text
language Option" for more info.)

Also, if you're FT Indexing HTML meta tags in columns with char/varcar or
TEXT datatypes on SQL Server 2000, you should consider porting this data to
a column with an IMAGE datatype as the new FTS "Image Filtering" now
supports and understands HTML meta tags. See SQL 2000 BOL title "Filtering
Supported File Types" for more info on this new feature!!
Regards,
John


Quote:> I've noticed a difference between SQL Server 7.0 and SQL Server 2000
> full-text search.  In a text field that contains:

> ... <TITLE>Microsoft</TITLE>  ...

> SQL Server 7.0 will but SQL Server 2000 will not return a result for the
> following queries:

> select * from table where contains(text_field, 'Microsoft') or
> select * from table where contains(text_field, '"Microsoft"')

> My 'default full-text language'  setting is for US english.
> exec sp_configure 'default full-text language'
> shows
> default full-text language          0           2147483647  1033
> 1033

> It appears that language neutral full-text is implicitly turned on for SQL
> Server 2000 because adding the <TITLE> start and end tags to the search
> string will return a result for SQL Server 2000.

> Steve


 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by swon » Fri, 19 Jan 2001 10:20:13


I have a SQL 7.0 installation on Win2k and a SQL 2000 installation on Win2k.
The SQL 7.0 search works and the SQL 2000 search does not.  Because both
version of SQL Server are installed on Win2k, the infosoft.dll were the
same.  Substituting a NT 4.0 version of the infosoft.dll did not solve the
problem.

We are considering using an image field in a future version of our
application but the current version is required to work on SQL 2000.

I compared infosoft.dll versions

> Steve,
> What OS do you have SQL Server 7.0 as well as your SQL Server 2000
installed
> on?

> If your SQL 7.0 is installed on NT 4.0, I suspect you are seeing the
correct
> behavior. However, if your SQL 2000 is installed on Win2K, I suspect
you're
> seeing the incorrect behavior. If so, you've encountered a recently filed
> bug on the Win2K wordbreaker (infosoft.dll). The issues is related to
> punctuation that is "right next to", i.e., no space between the letter and
> the punctuation. In your case, the < and >. You can revert back to the NT
> 4.0 behavior, via:
> Stopping the "Microsoft Search" service.
> Renaming the Win2K infosoft.dll to infosoft.old (or infosoft.w2k, in the
> winnt\system32 dir)
> Copying over the infosoft.dll from a NT 4.0 Server.
> Re-boot the Win2K Server and run a Full Population.

> BTW, your SQL Server 2000 is using the default full-text language of 1033
> that represents English_US (see SQL 2000 BOL title "default full-text
> language Option" for more info.)

> Also, if you're FT Indexing HTML meta tags in columns with char/varcar or
> TEXT datatypes on SQL Server 2000, you should consider porting this data
to
> a column with an IMAGE datatype as the new FTS "Image Filtering" now
> supports and understands HTML meta tags. See SQL 2000 BOL title "Filtering
> Supported File Types" for more info on this new feature!!
> Regards,
> John



> > I've noticed a difference between SQL Server 7.0 and SQL Server 2000
> > full-text search.  In a text field that contains:

> > ... <TITLE>Microsoft</TITLE>  ...

> > SQL Server 7.0 will but SQL Server 2000 will not return a result for the
> > following queries:

> > select * from table where contains(text_field, 'Microsoft') or
> > select * from table where contains(text_field, '"Microsoft"')

> > My 'default full-text language'  setting is for US english.
> > exec sp_configure 'default full-text language'
> > shows
> > default full-text language          0           2147483647  1033
> > 1033

> > It appears that language neutral full-text is implicitly turned on for
SQL
> > Server 2000 because adding the <TITLE> start and end tags to the search
> > string will return a result for SQL Server 2000.

> > Steve

 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by John Kan » Fri, 19 Jan 2001 11:46:26


Steve,
Are both SQL 7.0 and SQL 2000 on the SAME Win2K Server?
If so, then they are in fact using the same MSSearch engine as only the most
current copy of the "Microsoft Search" (mssearch.exe) is maintained. There
have been many changes, fixes and enhancements in SQL 2000 for FTS and to
get the SQL 2000 FTS queries to work correctly on HTML data, you will need
to put the data into a column defined with an IMAGE datatype and an
"extension" column (defined as sysname or char(3 or 4)) containing htm or
html.
Regards,
John


> I have a SQL 7.0 installation on Win2k and a SQL 2000 installation on
Win2k.
> The SQL 7.0 search works and the SQL 2000 search does not.  Because both
> version of SQL Server are installed on Win2k, the infosoft.dll were the
> same.  Substituting a NT 4.0 version of the infosoft.dll did not solve the
> problem.

> We are considering using an image field in a future version of our
> application but the current version is required to work on SQL 2000.

> I compared infosoft.dll versions


> > Steve,
> > What OS do you have SQL Server 7.0 as well as your SQL Server 2000
> installed
> > on?

> > If your SQL 7.0 is installed on NT 4.0, I suspect you are seeing the
> correct
> > behavior. However, if your SQL 2000 is installed on Win2K, I suspect
> you're
> > seeing the incorrect behavior. If so, you've encountered a recently
filed
> > bug on the Win2K wordbreaker (infosoft.dll). The issues is related to
> > punctuation that is "right next to", i.e., no space between the letter
and
> > the punctuation. In your case, the < and >. You can revert back to the
NT
> > 4.0 behavior, via:
> > Stopping the "Microsoft Search" service.
> > Renaming the Win2K infosoft.dll to infosoft.old (or infosoft.w2k, in the
> > winnt\system32 dir)
> > Copying over the infosoft.dll from a NT 4.0 Server.
> > Re-boot the Win2K Server and run a Full Population.

> > BTW, your SQL Server 2000 is using the default full-text language of
1033
> > that represents English_US (see SQL 2000 BOL title "default full-text
> > language Option" for more info.)

> > Also, if you're FT Indexing HTML meta tags in columns with char/varcar
or
> > TEXT datatypes on SQL Server 2000, you should consider porting this data
> to
> > a column with an IMAGE datatype as the new FTS "Image Filtering" now
> > supports and understands HTML meta tags. See SQL 2000 BOL title
"Filtering
> > Supported File Types" for more info on this new feature!!
> > Regards,
> > John



> > > I've noticed a difference between SQL Server 7.0 and SQL Server 2000
> > > full-text search.  In a text field that contains:

> > > ... <TITLE>Microsoft</TITLE>  ...

> > > SQL Server 7.0 will but SQL Server 2000 will not return a result for
the
> > > following queries:

> > > select * from table where contains(text_field, 'Microsoft') or
> > > select * from table where contains(text_field, '"Microsoft"')

> > > My 'default full-text language'  setting is for US english.
> > > exec sp_configure 'default full-text language'
> > > shows
> > > default full-text language          0           2147483647  1033
> > > 1033

> > > It appears that language neutral full-text is implicitly turned on for
> SQL
> > > Server 2000 because adding the <TITLE> start and end tags to the
search
> > > string will return a result for SQL Server 2000.

> > > Steve

 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by George Won » Sun, 21 Jan 2001 06:26:57


John,
No, they're on separate Win2K Servers.
If using IMAGE datatype is the solution, providing upward compatibility,
from existing Sql7 databases would require migrating the database table
column data(of type text) to the new datatype, IMAGE.  Also for existing
Sql2K databases, changing to the new datatype could take considerable time,
if they're large.
We'll have to do some testing to determine first if indeed using the IMAGE
datatype will do the trick.  We were hoping that some obtuse setting needed
to be set to correct the problem.


Quote:> Steve,
> Are both SQL 7.0 and SQL 2000 on the SAME Win2K Server?
> If so, then they are in fact using the same MSSearch engine as only the
most
> current copy of the "Microsoft Search" (mssearch.exe) is maintained. There
> have been many changes, fixes and enhancements in SQL 2000 for FTS and to
> get the SQL 2000 FTS queries to work correctly on HTML data, you will need
> to put the data into a column defined with an IMAGE datatype and an
> "extension" column (defined as sysname or char(3 or 4)) containing htm or
> html.
> Regards,
> John

 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by John Kan » Sun, 21 Jan 2001 08:49:53


George,
Yep, moving the HTML data and metatags from the a column defined as TEXT to
a column defined as IMAGE will provide upward compatibility and get you the
correct results for HTML. Yes, I know it'll take some effort (bcp out the
text column, drop the text column, add the image column and bcp in the data
to the image column), but it's better to "bite this bullet now, rather than
later", IMHO. Also, you might be able to automate this with a DTS package.
Regards,
John


> John,
> No, they're on separate Win2K Servers.
> If using IMAGE datatype is the solution, providing upward compatibility,
> from existing Sql7 databases would require migrating the database table
> column data(of type text) to the new datatype, IMAGE.  Also for existing
> Sql2K databases, changing to the new datatype could take considerable
time,
> if they're large.
> We'll have to do some testing to determine first if indeed using the IMAGE
> datatype will do the trick.  We were hoping that some obtuse setting
needed
> to be set to correct the problem.



> > Steve,
> > Are both SQL 7.0 and SQL 2000 on the SAME Win2K Server?
> > If so, then they are in fact using the same MSSearch engine as only the
> most
> > current copy of the "Microsoft Search" (mssearch.exe) is maintained.
There
> > have been many changes, fixes and enhancements in SQL 2000 for FTS and
to
> > get the SQL 2000 FTS queries to work correctly on HTML data, you will
need
> > to put the data into a column defined with an IMAGE datatype and an
> > "extension" column (defined as sysname or char(3 or 4)) containing htm
or
> > html.
> > Regards,
> > John

 
 
 

difference between SQL Server 7.0 and SQL Server 2000 full-text search

Post by John Kan » Sun, 21 Jan 2001 09:40:14


FYI, from a post to the microsoft.public.sqlserver.xml newsgroup.
John

We figured it out by way of a Stored Procedure.

CREATE PROCEDURE StemSearch

AS



SELECT     ID, NAME, DESCRIPTION, PRICE, MECID, THUMBNAIL, OEMID
FROM         PRODUCT

FOR XML AUTO
GO

Then the following template works.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
 <sql:param name='KEYWORD'>battery</sql:param>
</sql:header>
<sql:query >

</sql:query>
</ROOT>

 
 
 

1. Searching on XML-Tag with SQL Server 2000 and Full-Text

Hello everybody,

I successfully installed the  full-text catalog and the XML IFilter on
SQL server 2000. Searching xml documents stored in an IMAGE column works
very well.

When I search a xml document, sql server searches the whole document.
But that is not what I wan't to achieve. I want to search on the value
of an xml-tag.

Imagine the following xml:

<member>
  <name>Paul</name>
  <name>Steven</name>
</members>

Is it possible to say something like this:
where CONTAINS(columnname,'members/name = "Paul"') ?

I hope someone can help me.

Thanx in advance!

Grtz Vincent Kok

--
Posted via http://dbforums.com

2. Oracel exchange

3. SQL Server 2000 - Full Text Search - Where to Start

4. sqlConnection problem

5. SQL Server 2000:Incorrect results from full-text search on several columns

6. How to get rid of a failed SQL Server 2000 installation

7. Full-text search doesn't work on SQL Server 2000 Developers Edition

8. login failures

9. any document for full text search setup in sql server 2000

10. a couple of questions - win2k Adv server - SQL 2000 cluster - full text search

11. any document for full text search setup in sql server 2000

12. full-text searches on PDF files SQL Server 2000

13. SQL Server 2000: Full-Text Search Chat