collation issue

Hi
I have a problem with collation orders as follows.
The server collation order is set to Latin1_General_CI_AS.
How ever a few databases, a small number, have the collation order of
SQL_Latin1_General_CI_AS.
Now, when some operations are requested from the interograting App,
involving tempDB temp via sprocs and functions, I get a collation error, no
surprise there.
I have tried DTS, opps not the right tool, and scripts. I have nearly got
there with the scripts, but not quite.
So, to the question. What is the best way to amend the collation order of
the 'rogue' databases. And are there any *foolproof* solns that would do
this without the need for Extensive regression testing?
TIA
ChrisIn your select try something along the lines of
Select ...
From ..
Inner join tab1.Col1 = Tab1.Col2 Collate Database_Default
Can't remember the exact syntax but you can tell your query to use the
coillation of of the current database.
"Chris" <.@.> wrote in message
news:DNv7f.3599$iZ4.1238@.newsfe2-gui.ntli.net...
> Hi
> I have a problem with collation orders as follows.
> The server collation order is set to Latin1_General_CI_AS.
> How ever a few databases, a small number, have the collation order of
> SQL_Latin1_General_CI_AS.
> Now, when some operations are requested from the interograting App,
> involving tempDB temp via sprocs and functions, I get a collation error,
> no surprise there.
> I have tried DTS, opps not the right tool, and scripts. I have nearly got
> there with the scripts, but not quite.
> So, to the question. What is the best way to amend the collation order of
> the 'rogue' databases. And are there any *foolproof* solns that would do
> this without the need for Extensive regression testing?
> TIA
> Chris
>

collation error

Hi
I am building a database using the Latin1_General_CI_AS collation.
Everything seems to work fine but I am stuck with this one view. When
creating the view I get this error:
Server: Msg 446, Level 16, State 9, Procedure qfmFieldUserNames, Line 4
Cannot resolve collation conflict for equal to operation.
View:
CREATE VIEW qfmFieldUserNames
AS
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable = T1.name and D1.QFMColumn = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable = T1.name and
DL1.QFMColumn = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
Does anyone have any tips?
Many Thanks.Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause hav
e
different collations. You must declare which collation to use:
E.g.:
ColumnA has collation A
ColumnB has collation B
join ... on ColumnA collate B = ColumnB
or
join ... on ColumnA = ColumnB collate A
ML
http://milambda.blogspot.com/|||Thanks, I got to that too but all four columns are using the same collation
COLLATE SQL_Latin1_General_CP1_CI_AS
"ML" wrote:

> Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause h
ave
> different collations. You must declare which collation to use:
> E.g.:
> ColumnA has collation A
> ColumnB has collation B
> join ... on ColumnA collate B = ColumnB
> or
> join ... on ColumnA = ColumnB collate A
>
> ML
> --
> http://milambda.blogspot.com/|||Weird. Try the database collation that you mentioned in your OP:
Latin1_General_CI_AS (on all affected columns in the FROM clause).
Could be due to the fact that you're trying to create a view.
ML
http://milambda.blogspot.com/|||ok I got pass the problem by collating the Coalesce part of the view as well
as these columns seems to have a different collation.
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') collate
SQL_Latin1_General_CP1_CI_AS UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable collate SQL_Latin1_General_CP1_CI_AS
= T1.name and D1.QFMColumn collate SQL_Latin1_General_CP1_CI_AS = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable collate
SQL_Latin1_General_CP1_CI_AS = T1.name and DL1.QFMColumn collate
SQL_Latin1_General_CP1_CI_AS = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
"ML" wrote:

> Weird. Try the database collation that you mentioned in your OP:
> Latin1_General_CI_AS (on all affected columns in the FROM clause).
> Could be due to the fact that you're trying to create a view.
>
> ML
> --
> http://milambda.blogspot.com/|||Thank you, that's good to know.
ML
http://milambda.blogspot.com/

collation

Hi!
I am having a problem concerning collations.
I have an SQL Server 2000 with Latin1_General_CI_AS
which contains the company data.Some of the fields are Greek and although I
cannot view them correctly in the Query Analyzer , the tool we are using
(powerbuilder) displays them OK.
now that w e want to go Internet we are facing problems with the Greek
fields not displayes correctly...
What can we do in order to have them displayed correctly?
Thanx in advance!Hi
Do you define the column which displays the database as NAVARCHAR(n)?
"P Platan" <pplat@.exnds.com> wrote in message
news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I am having a problem concerning collations.
> I have an SQL Server 2000 with Latin1_General_CI_AS
> which contains the company data.Some of the fields are Greek and although
> I cannot view them correctly in the Query Analyzer , the tool we are using
> (powerbuilder) displays them OK.
> now that w e want to go Internet we are facing problems with the Greek
> fields not displayes correctly...
> What can we do in order to have them displayed correctly?
> Thanx in advance!
>|||unfortunately no!
it is an old database.migrated from ASA...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi
> Do you define the column which displays the database as NAVARCHAR(n)?
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> I am having a problem concerning collations.
>> I have an SQL Server 2000 with Latin1_General_CI_AS
>> which contains the company data.Some of the fields are Greek and although
>> I cannot view them correctly in the Query Analyzer , the tool we are
>> using (powerbuilder) displays them OK.
>> now that w e want to go Internet we are facing problems with the Greek
>> fields not displayes correctly...
>> What can we do in order to have them displayed correctly?
>> Thanx in advance!
>|||Well. please read article aboit UNICODE in the BOL
If so, you are going to alter the table and change the datatype
"P Platan" <pplat@.exnds.com> wrote in message
news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
> unfortunately no!
> it is an old database.migrated from ASA...
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Do you define the column which displays the database as NAVARCHAR(n)?
>>
>> "P Platan" <pplat@.exnds.com> wrote in message
>> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> I am having a problem concerning collations.
>> I have an SQL Server 2000 with Latin1_General_CI_AS
>> which contains the company data.Some of the fields are Greek and
>> although I cannot view them correctly in the Query Analyzer , the tool
>> we are using (powerbuilder) displays them OK.
>> now that w e want to go Internet we are facing problems with the Greek
>> fields not displayes correctly...
>> What can we do in order to have them displayed correctly?
>> Thanx in advance!
>>
>|||thanx!!
..what is BOL?
and if I alter the table will I be able to have the already stored Greek
fields displayed correctly?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Well. please read article aboit UNICODE in the BOL
> If so, you are going to alter the table and change the datatype
>
> "P Platan" <pplat@.exnds.com> wrote in message
> news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
>> unfortunately no!
>> it is an old database.migrated from ASA...
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Do you define the column which displays the database as NAVARCHAR(n)?
>>
>> "P Platan" <pplat@.exnds.com> wrote in message
>> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> I am having a problem concerning collations.
>> I have an SQL Server 2000 with Latin1_General_CI_AS
>> which contains the company data.Some of the fields are Greek and
>> although I cannot view them correctly in the Query Analyzer , the tool
>> we are using (powerbuilder) displays them OK.
>> now that w e want to go Internet we are facing problems with the Greek
>> fields not displayes correctly...
>> What can we do in order to have them displayed correctly?
>> Thanx in advance!
>>
>>
>|||> ..what is BOL?
Books On Line
> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
Yes, read the article in the BOL
"P Platan" <pplat@.exnds.com> wrote in message
news:OWcXWKGPGHA.2300@.TK2MSFTNGP15.phx.gbl...
> thanx!!
> ..what is BOL?
> and if I alter the table will I be able to have the already stored Greek
> fields displayed correctly?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23h1JdIGPGHA.1180@.TK2MSFTNGP09.phx.gbl...
>> Well. please read article aboit UNICODE in the BOL
>> If so, you are going to alter the table and change the datatype
>>
>> "P Platan" <pplat@.exnds.com> wrote in message
>> news:%23ttL1FGPGHA.2828@.TK2MSFTNGP12.phx.gbl...
>> unfortunately no!
>> it is an old database.migrated from ASA...
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e4ea9AGPGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Do you define the column which displays the database as NAVARCHAR(n)?
>>
>> "P Platan" <pplat@.exnds.com> wrote in message
>> news:Ohzqo8FPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> I am having a problem concerning collations.
>> I have an SQL Server 2000 with Latin1_General_CI_AS
>> which contains the company data.Some of the fields are Greek and
>> although I cannot view them correctly in the Query Analyzer , the tool
>> we are using (powerbuilder) displays them OK.
>> now that w e want to go Internet we are facing problems with the Greek
>> fields not displayes correctly...
>> What can we do in order to have them displayed correctly?
>> Thanx in advance!
>>
>>
>>
>sqlsql

collation

hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)