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
>