Repost: Databases with different collation

Let me try this one last time.
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.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> 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
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into DB2 on the case sensetive server and make DB2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into DB2 on the case sensetive server and make DB2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway
At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway
That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.