Repost: Databases with different collation

Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinYes, use unicode.
The problem is you can only have one collation per
database table, so combining two will cause a problem. The
answer is to use unicode ie ncar, nvarhar, ntext which is
collation independant.
J
>--Original Message--
>Hi group,
>we have databases now residing on different servers or
instances with
>different collations. We are considering bringing the
databases on the same
>server, keeping their respective collation.
>Some of our applications access databases with different
collations. We
>have been using linked server and specifying "Use remote
collation" (or
>not). But when putting the DBs with different collation
on the same server,
>you loss this feature. To go over all the applications
and specify the
>collation on the query statement level is of course too
big a task. Is
>there a way to do such a blanket declaration on the
database level?
>Or any other way of doing this efficiently?
>Your responses are greatly appreciated.
>Quentin
>
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> Yes, use unicode.
> The problem is you can only have one collation per
> database table, so combining two will cause a problem. The
> answer is to use unicode ie ncar, nvarhar, ntext which is
> collation independant.
Julie,
Are you suggesting that unicode columns ignore collation settings? That
is not true:
create table #un1 (a nvarchar(20) collate sql_latin1_general_cp1_ci_as)
create table #un2 (a nvarchar(20) collate sql_latin1_general_cp1_cs_as)
select * from #un1
union
select * from #un2
-- Server: Msg 446, Level 16, State 9, Line 5
-- Cannot resolve collation conflict for UNION operation.
If that wasn't what you meant, please explain in more detail.|||Sorry thats not what I meant. from the description two
different databases are being combined, the point I was
trying (unsuccessfully obviously) was that adding the data
from collation table x to table with collation y will
cause problems, so the best way is to convert them first
to unicode, then combine them.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
>> Yes, use unicode.
>> The problem is you can only have one collation per
>> database table, so combining two will cause a problem.
The
>> answer is to use unicode ie ncar, nvarhar, ntext which
is
>> collation independant.
> Julie,
> Are you suggesting that unicode columns ignore
collation settings? That
>is not true:
>
>create table #un1 (a nvarchar(20) collate
sql_latin1_general_cp1_ci_as)
>create table #un2 (a nvarchar(20) collate
sql_latin1_general_cp1_cs_as)
>select * from #un1
>union
>select * from #un2
>-- Server: Msg 446, Level 16, State 9, Line 5
>-- Cannot resolve collation conflict for UNION operation.
>
> If that wasn't what you meant, please explain in more
detail.
>
>.
>|||Julie/Adam,
Thanks for the responses.
Using unicode (or whatever casting) does not solve the problem. The problem
is that there are existing applications that use the linked server where
with the "use remote collation", you have the whole database covered, but
with any casting, you have only the column covered. You will have to go
over the entire code to do the change. What I am trying to find is rather a
solution that do such a blanket change so you do not need to worry where a
change is missed -- let alone to do all the changes.
Again thanks for the discussion. Any further comments are well come.
Quentin
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2bdab01c4682d$cf07d2f0$a301280a@.phx.gbl...
> Sorry thats not what I meant. from the description two
> different databases are being combined, the point I was
> trying (unsuccessfully obviously) was that adding the data
> from collation table x to table with collation y will
> cause problems, so the best way is to convert them first
> to unicode, then combine them.
> J
> >--Original Message--
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2a99d01c46820$c3396fe0$a601280a@.phx.gbl...
> >> Yes, use unicode.
> >>
> >> The problem is you can only have one collation per
> >> database table, so combining two will cause a problem.
> The
> >> answer is to use unicode ie ncar, nvarhar, ntext which
> is
> >> collation independant.
> >
> > Julie,
> >
> > Are you suggesting that unicode columns ignore
> collation settings? That
> >is not true:
> >
> >
> >create table #un1 (a nvarchar(20) collate
> sql_latin1_general_cp1_ci_as)
> >
> >create table #un2 (a nvarchar(20) collate
> sql_latin1_general_cp1_cs_as)
> >
> >select * from #un1
> >union
> >select * from #un2
> >
> >-- Server: Msg 446, Level 16, State 9, Line 5
> >-- Cannot resolve collation conflict for UNION operation.
> >
> >
> > If that wasn't what you meant, please explain in more
> detail.
> >
> >
> >.
> >