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.
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."
Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?
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?