Clustered DB Server: stated

Showing posts with label stated. Show all posts

Thursday, March 22, 2012

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.Have you tried converting the data to unicode before
concatination ?
Peter
"I favor the Civil Rights Act of 1964 and it must be
enforced at gunpoint if necessary."
Ronald Reagan
>--Original Message--
>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...
> > 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.
> >
>
>

Thursday, February 16, 2012

Clustered Vrs Non Clustered Indexes

> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
PeterSome definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The optimizer generates
execution plans where those plans might use indexes in different ways. And when the execution engine
uses the plans, using an index or different index types influences performance.
A blanket statement like "clustered index are fasten than non-clustered indexes" is too much of a
generalization. There are cases where using a non-clustered index will result in fewer I/O
operations and shorter execution time compared to using a clustered index. One such example is where
a non-clustered index covers the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter|||Thanks Tibor,
Ah well, wrong again ;)
Peter
>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index, is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>
>.
>|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
> different ways. And when the execution engine
>>uses the plans, using an index or different index types
> influences performance.
>>
>>A blanket statement like "clustered index are fasten than
> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using a
> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index, is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.|||So far over its caused a capsize :-D
>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
>> Thanks Tibor,
>> Ah well, wrong again ;)
>> Peter
>>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
>> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
>> different ways. And when the execution engine
>>uses the plans, using an index or different index types
>> influences performance.
>>
>>A blanket statement like "clustered index are fasten
than
>> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
>> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using
a
>> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
>> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index,
is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.
>
>.
>|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...
> So far over its caused a capsize :-D
>
>>--Original Message--
>>I wouldn't say "wrong", Pete. But perhaps an over-
> generalization... :-)
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
>>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
>> Thanks Tibor,
>> Ah well, wrong again ;)
>> Peter
>>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
>> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
>> different ways. And when the execution engine
>>uses the plans, using an index or different index types
>> influences performance.
>>
>>A blanket statement like "clustered index are fasten
> than
>> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
>> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using
> a
>> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
>> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index,
> is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.
>>
>>.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Don't you remember what Einstein said?
> He would rather have a watch that had stopped completely, than one that
lost
> a second a day.
> A watch that had stopped would still show the correct time twice every
day.
I hope this is apocryphal or metaphorical or just a joke. :)
Personally, I'd rather have a watch that lost a second a day.|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
>> Don't you remember what Einstein said?
>> He would rather have a watch that had stopped completely, than one
>> that lost a second a day.
>> A watch that had stopped would still show the correct time twice
>> every day.
> I hope this is apocryphal or metaphorical or just a joke. :)
> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Clustered Vrs Non Clustered Indexes

> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Thursday, March 22, 2012

collation conflict for concatenation operation

collation conflict for concatenation operation

collation conflict for concatenation operation

Thursday, February 16, 2012

Clustered Vrs Non Clustered Indexes

Clustered Vrs Non Clustered Indexes

Clustered Vrs Non Clustered Indexes

Clustered DB Server

Blog Archive

About Me