Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to use stopwords so that some words are skipped by the Full-Text Indexer. I can add those stopwords in the table sys.fulltext_stopwords.

When trying to get a list of and in SQL Server 2012 an error encountered. I'm executing the following (simplified) query:

SELECT sys.fulltext_stopwords.stopword 
FROM sys.fulltext_stopwords     

UNION 

SELECT sys.fulltext_system_stopwords.stopword 
FROM sys.fulltext_system_stopwords;

The error message I get is:

"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the UNION operation."

The database for the first SELECT statement in my query is Latin1_General_CI_AS. The same is true for the , , and databases.

Where does the Latin1_General_BIN come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

EDIT:

I can 'solve' my error by using COLLATE in my query, like this:

SELECT  sys.fulltext_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_stopwords 

UNION 

SELECT sys.fulltext_system_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_system_stopwords 

I see that the system-stopwords are stored in the resource database, which can explain the difference in collation. The next question would be: why is the collation from the resource database different that the default?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.