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 stopwords and system-stopwords 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 collation for the first SELECT statement in my query is Latin1_General_CI_AS. The same is true for the master, model, msdb and tempdb databases.
Where does the Latin1_General_BIN collation 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?
