collation conflict resolution

I have a number of databases with quite similar structure, but with
different COLLATION
settings. Unfortunately when the structure (including keys and constraints)
has been created
no one took care of COLLATION at all.
At the moment statements like:
--
SELECT
. . .
(case FieldA
when 1 then (select Source1 from Table1 WHERE ...)
when 2 then (select Source2 from Table2 WHERE ...)
end) as Result,
--
gives COLLATION CONFLICT because Table1 and Table2 have different collation
setting.
But for a half of number of tables it works fine.
What aa I asking about: How to change COLLATION SETTING "on fly" for
different tables.fields ?
May be someone knows any fancy tools or "script wizards" to do so?
Please don't waste this newsgroup space with offering me to insert "COLLATE
DATABASE_DEFAULT"
to each statement which causes such problem. Ok?
Thanks a lot!
GnumHi,
CREATE VIEW FROM Table2
ADD COLLATE AFTER EACH FIELDS.
> when 1 then (select Source1 from Table1 WHERE ...)
(select Source1 from View1 WHERE ...)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2004
"Gnum" <no-email@.mail.boo> wrote in message
news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> I have a number of databases with quite similar structure, but with
> different COLLATION
> settings. Unfortunately when the structure (including keys and
constraints)
> has been created
> no one took care of COLLATION at all.
> At the moment statements like:
> --
> SELECT
> . . .
> (case FieldA
> when 1 then (select Source1 from Table1 WHERE ...)
> when 2 then (select Source2 from Table2 WHERE ...)
> end) as Result,
> --
> gives COLLATION CONFLICT because Table1 and Table2 have different
collation
> setting.
> But for a half of number of tables it works fine.
> What aa I asking about: How to change COLLATION SETTING "on fly" for
> different tables.fields ?
> May be someone knows any fancy tools or "script wizards" to do so?
> Please don't waste this newsgroup space with offering me to insert
"COLLATE
> DATABASE_DEFAULT"
> to each statement which causes such problem. Ok?
>
> Thanks a lot!
> Gnum
>|||Thank you very much for so big
MISUNDERSTANDING!
Have you ever read the question up to the end?
I've written there "
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?"
I need a help, but not a collection of stupid wisdom-like erudition esseys!
Thanks!
"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote in message
news:OOcY6TlvDHA.2880@.tk2msftngp13.phx.gbl...
> Hi,
> CREATE VIEW FROM Table2
> ADD COLLATE AFTER EACH FIELDS.
> > when 1 then (select Source1 from Table1 WHERE ...)
> (select Source1 from View1 WHERE ...)
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2004
> "Gnum" <no-email@.mail.boo> wrote in message
> news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> > I have a number of databases with quite similar structure, but with
> > different COLLATION
> > settings. Unfortunately when the structure (including keys and
> constraints)
> > has been created
> > no one took care of COLLATION at all.
> > At the moment statements like:
> > --
> > SELECT
> > . . .
> > (case FieldA
> > when 1 then (select Source1 from Table1 WHERE ...)
> > when 2 then (select Source2 from Table2 WHERE ...)
> > end) as Result,
> > --
> > gives COLLATION CONFLICT because Table1 and Table2 have different
> collation
> > setting.
> > But for a half of number of tables it works fine.
> >
> > What aa I asking about: How to change COLLATION SETTING "on fly" for
> > different tables.fields ?
> > May be someone knows any fancy tools or "script wizards" to do so?
> >
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?
> >
> >
> > Thanks a lot!
> >
> > Gnum
> >
> >
>