collation conflict for concatenation operation

Hi,
I'm facing the error as the subject stated , "Cannot resolve collation
conflict for concatenation operation".
Base on running "print cast(
databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
I want to do searching on records in tables with partial matching on the
keywords.
the sql statement is :
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? + '%' ) )
)
order by co.active_from_date desc
as I will run this statement in java, the "?" will be filled into string and
both fields are nvarchar.
how should I do to solve this problm? thank you.Try adding collation designators to the LIKE predicates:
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
OR (title_en like '%' + ? + '%' collate
inese_Taiwan_Stroke_CI_AS) ) )
order by co.active_from_date desc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"little C" <little C@.discussions.microsoft.com> wrote in message
news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> Hi,
> I'm facing the error as the subject stated , "Cannot resolve collation
> conflict for concatenation operation".
> Base on running "print cast(
> databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> I want to do searching on records in tables with partial matching on the
> keywords.
> the sql statement is :
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
' ) ) )
> order by co.active_from_date desc
> as I will run this statement in java, the "?" will be filled into string
and
> both fields are nvarchar.
> how should I do to solve this problm? thank you.
>|||thanks Adam,
the problem is solved, I need to put "collate Chinese_Taiwan_Stroke_CI_AS"
right next to each "?". thanks a lot.
Chris C
"Adam Machanic" wrote:

> Try adding collation designators to the LIKE predicates:
>
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
> OR (title_en like '%' + ? + '%' collate
> inese_Taiwan_Stroke_CI_AS) ) )
> order by co.active_from_date desc
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "little C" <little C@.discussions.microsoft.com> wrote in message
> news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> ' ) ) )
> and
>
>