Change collation in SQL2005 server

To change the collation you can use following script;

1. Note down the Database Collation. By right clicking on the database.
2. Take a backup of database to be on a safer side...
3. Note down locations of all database files...
4. Detach all user-Database...
5. Run following commands.

TO CHANGE GLOBAL SERVER COLLATION FOR SQL SERVER 2005:

Running the following command from the "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap" folder.
Make sure the SQL Server 2005 original CD is available:

Code:->

CD "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap"

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=Password@123 SQLCOLLATION=Latin1_General_CI_AI

6. Attach all require database...
7. Create users; & give ownership of appropriate database using following script;

use digite

go

EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','DIGITE','DIGITE'

8. If there are more no of users you can transfer logins by following instructions given in below mentioned site.

http://support.microsoft.com/kb/918992/