Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-09 : 10:07:00
|
| Steve writes "I am attempting to return a query combining the results of an MS Index Server query (indexing html files on the filesystem) and results from Full-Text Indexed SQL tables.I completed this (with a fair amount of effort) in SQL7, however I'm having no luck porting this same query to a SQL2000 environment. The error I get is:"Cannot resolve collation conflict for UNION operation."Here is an extract of my query. 'Filesystem' is the name I nominated via sp_addlinkedserver that points to my Index Server catalog. I've simplified the SELECT stmt in the 2nd query to improve readability.<snip>SELECT 'idxsrv' as DocType, *FROM OpenQuery( FileSystem, 'SELECT DocTitle, vpath, filename, size, write, characterization, rank FROM SCOPE() WHERE CONTAINS( Contents, ''beer'' ) ' )UNION SELECT 'bulletin' "DocType", '' "DocTitle", '' "vpath", ''"filename", 0 "size", '' "write", '' "characterization", KEY_TBL.Rank "rank"FROM tblBulletinBoard bb INNER JOIN CONTAINSTABLE( tblBulletinBoard, *, 'beer' ) AS KEY_TBL ON bb.BulletinId = KEY_TBL.[KEY] ORDER BY rank DESC</snip>As I said, this query works fine under SQL7.Now, I've taken the CREATE TABLE scripts from this database andrebuilt it all manually, including some data, in SQL2000. The index server catalog and full-text indexing catalogs have also been built and refreshed. However when I run the same code on this server, the aforementioned error is returned.I've read just about all COLLATION related articles in BOL and via GOOGLE searches until I passed out and threw up. Based on myinvestigations, I've tried the following:1) Included a 'COLLATE Latin1_General_CI_AS' stmt before the ORDER BY clause. I got a different error this time:"Expression type int is invalid for COLLATE clause."Even after removing all columns with an int datatype from the SELECT stmts, this mysterious error kept appearing.2) I returned to my original query and I used the sp_serveroption to set 'collation compatible' to 'true' on the FileSystem linkedserver. Did not fix.3) I then used the sp_serveroption to set the 'remote collation' to 'true' and the 'collation name' to 'Latin1_General_CI_AS' on both the FileSystem linkedserver and the Database name. Did not fix.I've also used 'sp_helpdb mydatabasename' to check the collation value on the database. Lo and behold it is Latin1_General_CI_AS - the same as my Index Server linkedserver. So why then does my UNION stmt complain of a collation conflict ? Running either of the two queries by themselves works ok - it's just when I attempt to UNION the results the whole thing goes pair-shaped.I am not interested in supporting multi-language character sets or anything like that, so the basic vanilla flavoured collation is all I want.Surly I can't be the only poor soul on the planet to attempt this. Any hints, tips and/or tricks would be very much appreciated.Many thanks in advance ...Steve" |
|
|
|
|
|
|
|
Collation conflict on UNION in SQL2000 involving Indexing Service