chinese full-text search

Hello,
Request your help on querying a Fulltext indexed table with Chinese Text.
I have sql server 2000, and a database with Chinese collation, a table is
full text indexed with Chinese(RPC) word breakers.
I am able to retrieve the Chinese text from the table and successfully
display the same on the browser but am not able to search.
What is happening is when I run the below query in query analyzer, it is
returning me a result set
SELECT * from DCNews_Live where contains (*,' "chinese text" ')
Where as when I run the same query from ASP (Active Server Pages) it is not
returning any values.
Can u explain where I am going wrong?
The ASP code that I use is
strSearch = trim(request.Form("txtSearch"))
strSql = "SELECT * from Table where contains(*,' """ & strSearch
& """ ')"
objRs.Open strSql,objCon
The Recordset is empty.
Can u please help out of this
Regards,
Prudhvi Raju M
Did you set your session code page for Chinese simplified or Traditional?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
> Hello,
> Request your help on querying a Fulltext indexed table with Chinese Text.
> I have sql server 2000, and a database with Chinese collation, a table is
> full text indexed with Chinese(RPC) word breakers.
> I am able to retrieve the Chinese text from the table and successfully
> display the same on the browser but am not able to search.
>
>
> What is happening is when I run the below query in query analyzer, it is
> returning me a result set
>
> SELECT * from DCNews_Live where contains (*,' "chinese text" ')
>
> Where as when I run the same query from ASP (Active Server Pages) it is
> not
> returning any values.
>
> Can u explain where I am going wrong?
>
> The ASP code that I use is
>
> strSearch = trim(request.Form("txtSearch"))
>
> strSql = "SELECT * from Table where contains(*,' """ &
> strSearch
> & """ ')"
>
> objRs.Open strSql,objCon
>
> The Recordset is empty.
>
> Can u please help out of this
>
> Regards,
> Prudhvi Raju M
>
>
|||I've haven't set the session code page but included the meta tag
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
"Hilary Cotter" wrote:

> Did you set your session code page for Chinese simplified or Traditional?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
> news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
>
>
|||You need to set the code page in your asp page.
Run profiler to see the command that hits SQL Server. I think what is
happening is that the chinese characters being entered on your web server
are not making to SQL Server as Chinese characters, rather their character
representations in the 1252 code page. Hence no hits
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...[vbcol=seagreen]
> I've haven't set the session code page but included the meta tag
> <META http-equiv=Content-Type content="text/html; charset=windows-1252">
> "Hilary Cotter" wrote:
|||Thanks Hilary for the advice.
I've included BIG5 codepage and also changed the meta tag to
<META http-equiv=Content-Type content="text/html; charset=big5">
and am now able to get the result set for chinese search.
Thank You.
Regards,
Prudhvi
"Hilary Cotter" wrote:

> You need to set the code page in your asp page.
> Run profiler to see the command that hits SQL Server. I think what is
> happening is that the chinese characters being entered on your web server
> are not making to SQL Server as Chinese characters, rather their character
> representations in the 1252 code page. Hence no hits
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
> news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...
>
>

Checking for existence of a table that is already full-text indexe

Full-text indexing was manually set up to work on the development server.
Now, I need to write a SQL script to programmatically set up Full-text
indexing on the staging server and then on the production server.
The SQL script must be rerunnable. It should handle the scenario where
full-text indexing already exists or not, per table, as necessary.
The SQL script works on the first pass successfully (because the staging
server did not have full-text indexing).
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
The SQL script fails on the second pass on the staging server. Because it
tries to create the index that already exists.
So, I added a statement to drop the index before creating the index.
EXEC sp_fulltext_table 'tablename', 'drop'
GO
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
That works because it drops an existing index, then creates the index.
However, the drop statement will fail if an index doesn't exist.
So then, how do I check for the existence of a table index before dropping it?
IF EXISTS ("statement to check for existence of a table index")
BEGIN
EXEC sp_fulltext_table 'tablename', 'drop'
GO
END
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that I
could use in the if statement.
EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
GO
Any suggestions?
MGBloomfield,
Yes, there are some good T-SQL code examples can be found & modified for
your purposes in the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at:
http://support.microsoft.com/default...b;EN-US;240867
You might also find this code useful as well:
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
Regards,
John
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:F80D1509-E8EF-4B4F-9011-BA44DF7EF91C@.microsoft.com...
> Full-text indexing was manually set up to work on the development server.
> Now, I need to write a SQL script to programmatically set up Full-text
> indexing on the staging server and then on the production server.
> The SQL script must be rerunnable. It should handle the scenario where
> full-text indexing already exists or not, per table, as necessary.
> The SQL script works on the first pass successfully (because the staging
> server did not have full-text indexing).
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> The SQL script fails on the second pass on the staging server. Because it
> tries to create the index that already exists.
> So, I added a statement to drop the index before creating the index.
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> That works because it drops an existing index, then creates the index.
> However, the drop statement will fail if an index doesn't exist.
> So then, how do I check for the existence of a table index before dropping
it?
> IF EXISTS ("statement to check for existence of a table index")
> BEGIN
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> END
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that
I
> could use in the if statement.
> EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
> GO
> Any suggestions?
>