Posted by Erland Sommarskog on 08/21/06 21:56

Nasir (nmajeed@prosrm.com) writes:
> What is the best way to resolve these collation errors:
>
> My server is installed as - Latin1_General_CS_AS, but one of the
> important 50GB databases migrated from another server has -
> SQL_Latin1_General_CP1_CS_AS, and we are getting errors:
>
> Msg 468, Level 16, State 9, Line 1
> Cannot resolve the collation conflict between "Latin1_General_CS_AS" and
> "SQL_Latin1_General_CP1_CS_AS" in the equal to operation
>
> WHat sthe best way to resolve these problems...would bcp in/out fix it?

You would have to rebuild the database from scripts in such case.

The quickest and easiest fix may be to simply install a second instance
with SQL_Latin1_General_CP1_CS_AS as the server collation.

You can also run:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name etc

and cut and paste the result into a query window. Unforunately, it is not
that easy, if columns are indexed, or referenced by foreign keys, so
indexes have to be dropped and reapplied, same goes for constraints.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]