July 27, 2004 at 9:43 pm
Please help me with the following 2 questions:
1.- I have SQL Server 2000 Developer Edition installed in a PC with Windows XP Professional. Is it possible to install and run full-text indexing in this environment? If the answer is yes, how do I install it?
2.- I'm trying to use stored messages in SQL Server. First I record the message (50001) in English and then in Spanish. When I run "raiserror(50001,16,1)" the message appears in English, what should I do to make it appear in Spanish?
Thanks in advance,
Xavier Castillo.
July 29, 2004 at 7:03 am
I have to admit I've never used raiseerror, but I would assume it uses the default locale for your instance of SQL, or for the user that is executing the query.
Try looking up SET LANGUAGE in BOL that may give you the solution you need.
HTH
Tony
July 29, 2004 at 8:30 am
In regards to your first question, the answer is Yes it is possible to install and run full-text search (FTS). SQL FTS is not installed by default with the SQL 2000 Developers Edition and you need to use your SQL installation CD and select "custom installation" and under "Server components" select "Full-Text Search" and it will install all the necessary FTS & MSSearch components. Additionally, if you have applied any SQL Server 2000 Service Packs, you should re-install them again, so that the SQL FTS & MSSearch components will also get updated to the same SP level.
John T. Kane
August 1, 2004 at 3:29 pm
Thank you. I installed full-text search and SP3 successfully.
Xavier Castillo.
August 1, 2004 at 3:31 pm
I tried "set language Spanish" and also "exec sp_defaultLanguage". They changed the language to Spanish, but now the "raiserror" command doesn't recognize any messages (neither English nor Spanish).
Thank you.
Xavier Castillo.
August 1, 2004 at 3:58 pm
Xavier,
Can I assume that the language in your FT-enabled column is Spanish? If so, then you need to use system stored procedure "sp_fulltext_column". The below workaround is from a bug I filed that "Spanish" was missing from the FT Wizard's dropdown list of "Language of Word Breaker", However, Spanish is referenced as a valid Full-Text Search language in the SQL 2000 BOL title "default full-text language Option":
The following linguistic analysis packages are part of Microsoft® SQL Server 2000.
Language Setting
============= =====
Spanish Modern 3082
Workaround:
==========
Use the Full-Text Search system stored procedures (sp_fulltext_column) to correctly identify and use the Spanish Language word breaker, for example:
use pubs
go
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add', 0xC0A -- Locale = Spanish_Modern and Locale ID = 0x0c0a
go
Regards,
John T. Kane
August 9, 2004 at 12:58 pm
John:
After creating the full-text catalog, I ran a select query and got the following error:
Unexpected NULL value returned for column '[FULLTEXT:provincia].KEY' from the
OLE DB provider 'Full-text Search Engine'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned for
the column: ProviderName='Full-text Search Engine',
TableName='[FULLTEXT:provincia]', ColumnName='KEY'].
*--------------------------------------------
The server collation is: Traditional_Spanish_CI_AS
The database collation is: Modern_Spanish_CI_AI
These 2 collations are different because when I installed SQL Server 2000 Developer Edition in my laptop, in January 2004, I didn't know anything about SQL Server, so I just installed it. After several months of working with SQL Server I realized the second collation is more appropriate for indexes, so I changed the database collation.
*--------------------------------------------
As an example, I'm full-text indexing table "provincia". The relevant fields in this table are:
iProvPk smallint identity not null, -- Primary key name is "provincia_Pk"
cNompro char(20) not null
The table has only 22 rows. There is already a unique index on "cNompro".
*--------------------------------------------
I created and ran the following procedure to create the full-text index.
CREATE PROCEDURE dbo.cp_fti
AS
set nocount on
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'catOK1', 'create'
exec sp_fulltext_table 'provincia', 'create', 'catOK1', 'provincia_Pk'
exec sp_fulltext_column 'provincia', 'cNompro', 'add', 0x0c0a
exec sp_fulltext_table 'provincia', 'activate'
exec sp_fulltext_table 'provincia', 'start_full'
exec sp_fulltext_table 'provincia', 'start_change_tracking'
exec sp_fulltext_table 'provincia', 'start_background_updateindex'
GO
*--------------------------------------------
After this I checked the properties of catOK1 and got the following:
Status: Idle.
Item count: 23
Unique key count: 113
*--------------------------------------------
This is the select statement to test the full-text index (the statement that returns the error message):
select * from provincia where contains(cNompro,'LOJA')
*--------------------------------------------
I changed the language in Query Analyzer (set language Spanish) and ran the query again.
I also dropped the full-text catalog and created it again using the wizard. This time I chose "us_english" as the "Language for Word Breaker".
I ran the query again in Query Analyzer (after running "set language us_english").
I get the same error message no matter what I do.
Thank you,
Xavier Castillo.
PS: sorry I couldn't reply before.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply