Sql-server – How to convert an MS SQL 2005 DB from Collation SQL_Latin1_General_CP1_CI to SQL_Latin1_General_CP1_C1_AS

sqlsql serversql-server-2005

How can I convert an MS SQL 2005 DB from collation SQL_Latin1_General_CP1_CI to SQL_Latin1_General_CP1_C1_AS?

SQL_Latin1_General_CP1_C1_AS does not appear in the list of options when I go to:

Right click on the DB and select 'Properties'
Options
Collation

Best Answer

I think the collation you're looking for is CI, with the letter I, not the number 1. Check the screen again, but if you still don't see it, try running this script with your database name in it:

USE [master]
GO
ALTER DATABASE [MyDatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Related Solutions

Sql-server – SQL Server 2005 collation issues

If the problem is a query and you have not the option to rebuild that database, you could go with the COLLATE keyword, for example:

 SELECT *
 FROM OtherServer.xyz.dbo.Table2 t2
     inner join myTable t1 
         on t1.name = t2.name COLLATE SQL_Latin1_General_CP1_CI_AS

Here is the MSDN documentation for COLLATE.

Sql-server – How to change the collation of a SQL Server Database

MS KB 325335 has options on how to do this for the whole db and all columns.

Basically:

Script database tables (with new collation)
DTS/SSIS data (watching collation)
Add constraints

Best Answer

Related Solutions

Sql-server – SQL Server 2005 collation issues

Sql-server – How to change the collation of a SQL Server Database

Related Topic