Can we change the collation in SQL 2005..?

Question

Post reply

Can we change the collation in SQL 2005..?

sanjeev_krs2004

Default port

Points: 1462
More actions
September 2, 2008 at 7:21 am

#154683

Hi,
I want to change the collation of SQL 2005. Can any of you help me..?:)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

You can change it on the user databases, but in order to change it on the system databases you have to reinstall SQL.

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 2

Thanks for a quick reply:)

could you please tell me how we can do it..?

Thanks

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

For a user database it would be something like this.

USE [master]

GO

ALTER DATABASE [DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS

GO

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Once you change it at the database level you will need to update all the text related columns as they are assigned the database collation and will retain that even after you change it at the DB level. New tables / columns will be created with the new collation.

The script below will change the collation (you will need to specify the proper collation) and will script out the changes to all the tables.

alter database YourDBName collate SQL_Latin1_General_CP1_CI_AS

select distinct

'ALTER TABLE '+so.name+' ALTER COLUMN ['+sc.name+'] '+st.name+'('+convert(varchar(20), sc.length)+') COLLATE '+st.collation_name

from

sys.sysobjects so join sys.syscolumns sc

on so.id = sc.id

join sys.types st

on sc.xtype = st.user_type_id

where

st.user_type_id in (35, 99, 167, 175, 231, 239, 256)

and so.xtype = 'U'

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 5

Fantastic you guys are rockin:D

David,

Could you please explain me a bit about:

st.user_type_id in (35, 99, 167, 175, 231, 239, 256)

What are these values...

Thanks in advance.

SQL dala SSC Enthusiast Points: 152 More actions · Answer 6

You can find this in the books online section "How to: Install SQL Server 2005 from the Command Prompt "

You can only change the server collation settings if you do not use Analyses services on the same server.`

You need to execute the following command from the command prompt where your sql server is installed from:

setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION=<NewSystemCollation

See for more information Books online

Before reinstalling SQL server be sure to script your logins and backup your databases.

You can also detach your userdb's and attach them again after the reinstall and add your logins again.

Arjen Dalenberg

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

Those are the datatypes that would be affected by collation settings. If you select * from sys.types you should see the datatypes represented in the query. You should definitely check my logic to make sure that I am getting everything covered.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot