Database

Re: Remove a noise word in a SQL Server 2008 database

12-Jun-12 13:55

Hello all. I created a DB with a single table, with two columns enabled for full-text search. Everything works fine. However, whenever I lookup for a word ("salud", with means "health" in Spanish), it throws no results:

SQL

select id, vision, age, gender, [state], tier1
from visions
where contains(tier1, 'Salud')

Frustrated with this, I started looking on the causes. I came across an article that suggested to run a parse on the word, and so I did:

SQL

SELECT * FROM sys.dm_fts_parser (' "Salud" ', 3082, 0, 0)

This returned the following record:

0x00730061006C00750064	1	0	1	Noise Word	salud	0	Salud

So, to my dismay, "salud" is a Noise Word. I've been trying to search how to stop "salud" from being a noise word, but all I find is reference to some noisees files, which I'm unable to find in my SQL Server 2008 installation.

Thus my question: does anybody know where can I remove "salud" from being a noise word? Any clue on this matter will be reaaaally appreciated!

Thanks in advance. Best regards.

Bernhard Hiller13-Jun-12 0:00

Bernhard Hiller

13-Jun-12 0:00

The "stop words" are in a system view.

SQL

SELECT [stopword], [language_id]
  FROM [master].[sys].[fulltext_system_stopwords]
  where language_id=3082

I tried a DELETE query:

SQL

delete
FROM [master].[sys].[fulltext_system_stopwords]
where [stopword]='salud' and language_id=3082

but that caused an exception:
"Ad hoc updates to system catalogs are not allowed"
I do not have further ideas...

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:05

Re: Remove a noise word in a SQL Server 2008 database

13-Jun-12 5:05

Hi, thanks so much for your help!

I finally managed to remove the noise words. It seems that MSSQLSVR2K8R2 (Uf!) has hardcoded somewhere (apparently some "resource" database, whatever that is) the noise words for each language. So, what I did was:

1.- Create my own stoplist[^]
2.- Through the SQL Management Studio, open the full-text index properties and then select the recently created stoplist rather than the default.
3.- Alter[^] the stoplist and drop the noise word.
4.- Re-index the full-text index.

Thanks again for your help! Smile | :)

Eddy Vluggen13-Jun-12 1:24

Re: Remove a noise word in a SQL Server 2008 database

13-Jun-12 1:24

Those are stored as plain text-files Smile | :)

See MSDN[^].

Bastard Programmer from Hell Suspicious | :suss:

Fernando A. Gomez F.13-Jun-12 5:06

Re: Remove a noise word in a SQL Server 2008 database

13-Jun-12 5:06

Yeh, tried looking for those files, yet didn't find 'em on MSSQLSVR 2008. I found the Thresaurus, mind you, but not the words. I read in MSDN that in 2008 they were stored in the "Resources" database, yet I wasn't able to find 'em.

Thanks for the insight!

Eddy Vluggen13-Jun-12 5:48

Re: Remove a noise word in a SQL Server 2008 database

13-Jun-12 5:48

Fernando A. Gomez F. wrote:
MSSQLSVR 2008

I should have read more carefully.. As I read, you did find 'em by now Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Bernhard Hiller13-Jun-12 4:40

Bernhard Hiller

13-Jun-12 4:40

Eddy Vluggen supplied an important hint - but the version is different. Look at http://msdn.microsoft.com/en-us/library/cc280871%28v=sql.100%29.aspx[^]

Re: Remove a noise word in a SQL Server 2008 database

Fernando A. Gomez F.13-Jun-12 5:07

Re: Remove a noise word in a SQL Server 2008 database

13-Jun-12 5:07

Ah, yes, that did it! Find 'em yesterday, very late at night, when I was cursing the DB builders. Laugh | :laugh:

Thanks, cheers!

Fernando A. Gomez F.13-Jun-12 5:08

SQL Compound Primary Keys

13-Jun-12 5:08

Why the heck is "salud" (health) a noise word, escapes my comprehension though.

Kevin Marois12-Jun-12 11:36

12-Jun-12 11:36

I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical.

Here's the tables:

CREATE TABLE tblOperators
	(OperatorId		INT	IDENTITY		NOT NULL,
	 AccountNo		VARCHAR(10)			NULL,
	 [Name]			VARCHAR(100)		NOT NULL,
	 Address1		VARCHAR(100)		NOT NULL,
	 Address2		VARCHAR(100)		NULL,
	 City			VARCHAR(100)		NOT NULL,
	 [State]		VARCHAR(2)			NOT NULL,
	 ZipCode		VARCHAR(10)			NOT NULL,
	 Phone			VARCHAR(10)			NOT NULL,
	 Email			VARCHAR(100)		NULL,
	 IsActive		BIT					NULL)


CREATE TABLE tblFacilities
	(FacilityId			INT	IDENTITY		NOT NULL,
	 OperatorId			INT					NOT NULL,
	 FormOfPaymentId	INT					NULL,
	 SaleTypeId			INT					NULL,
	 FacilityName		VARCHAR(100)		NULL,
	 FacilityAddress1	VARCHAR(100)		NULL,
	 FacilityAddress2	VARCHAR(100)		NULL,
	 FacilityCity		VARCHAR(100)		NULL,
	 FacilityState		VARCHAR(02)			NULL,
	 FacilityZip		VARCHAR(10)			NULL,
	 FacilityPhone		VARCHAR(10)			NULL,
	 BankName			VARCHAR(100)		NULL,
	 ManagerName		VARCHAR(100)		NULL,
	 ManagerPhone		VARCHAR(10)			NULL,
	 ManagerEmail		VARCHAR(100)		NULL,
	 PickupDays			VARCHAR(100)		NULL,
	 IsActive			BIT					NULL)

You can see the Operator and Facility Id's. They won't work once sent to the server.

I'm not sure how to define compound keys. Can someone point me in the right direction?

Thanks

If it's not broken, fix it until it is

PIEBALDconsult12-Jun-12 16:45

PIEBALDconsult

12-Jun-12 16:45

That is a big reason not to use integers as keys.

You may need to rethink your whole application.
I mainly wonder why the clients would be adding records to these tables.

However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.

Kevin Marois12-Jun-12 18:13

12-Jun-12 18:13

PIEBALDconsult wrote:
You may need to rethink your whole application.
I mainly wonder why the
clients would be adding records to these tables.

What if the client doesn't have a network connection? Where would the store the data. This application cannot have down time in the event of a lost connection. So the app will have a local data store and update the server periodically.

So, since new records can be added in the client's DB, I need to make sure the PK's are unique.

Thanks

If it's not broken, fix it until it is

PIEBALDconsult13-Jun-12 4:10

PIEBALDconsult

13-Jun-12 4:10

Yes, but why would the client be adding records to those tables?

Kevin Marois wrote:
PK's are unique

Globally unique?

Kevin Marois13-Jun-12 6:33

13-Jun-12 6:33

I don't understand why you're questioning this.

The software is installed locally on a user's PC, along with a local copy of the DB. The software does CRUD operations on the local copy of the DB.

Then, at night, or at least once per week, the software uploads all new/changed/deleted records to the server.

If it's not broken, fix it until it is

Eddy Vluggen13-Jun-12 1:21

13-Jun-12 1:21

PIEBALDconsult wrote:
That is a big reason not to use integers as keys.

Yeah, people being too lazy. The problem goes away with GUIDs, since that allows for laziness.

Bastard Programmer from Hell Suspicious | :suss:

Jörgen Andersson12-Jun-12 21:56

Jörgen Andersson

12-Jun-12 21:56

Kevin Marois wrote:
and each night will upload its data to the server

Is this a oneway communication where the data is only uploaded, or will it synchronise the data with the server?
If it's just an upload you don't need to have global keys, you can add new keys at the server when uploading.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Kevin Marois13-Jun-12 6:34

13-Jun-12 6:34

There will be two-way communicaation

If it's not broken, fix it until it is

Eddy Vluggen13-Jun-12 1:19