Posted by Erland Sommarskog on 04/11/06 00:38

(sdwebguy@gmail.com) writes:
> We have a server that was running SQL 2000. Complete uninstall of 2000.
> Complete fresh install of SQL 2005. Then we used the 2000 to 2005
> migration wizard to copy the app databases from another 2000 server to
> the new 2005 server.
>
> When I run these statements:
> select name, collation_name, compatibility_level from sys.databases
> select serverproperty('Collation')
>
> I get these results:
>
> master Latin1_General_CI_AI 90
> tempdb Latin1_General_CI_AI 90
> model Latin1_General_CI_AI 90
> msdb Latin1_General_CI_AI 90
> appdb1 SQL_Latin1_General_CP1_CI_AS 90
> appdb2 SQL_Latin1_General_CP1_CI_AS 90
>
> Latin1_General_CI_AI
>
> We get errors in our app because we use tempDB for some ## temp tables.
>
>
> On our development server, the system databases are all
> SQL_Latin1_General_CP1_CI_AS and the serverproperty('Collation') also
> returns SQL_Latin1_General_CP1_CI_AS. So Im not sure what the best way
> to proceed is.

First you should make a decision on which collation you want to
use. SQL collations gives somewhat better performance they say, but
I believe Windows collations gives somewhat better linguistic behaviour.

But since the difference between SQL_Latin1_General_CP1_CI_AS and
Latin1_General_CI_AI may not be significant to you, and it's a bit
of a hassle to change the collation for all objects in a database, the
is probably to uninstall SQL 2005, and then reinstall, this time
paying more attention to the collation dialogue. After this you would
redo the migration.

--
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]