change collation in sql 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

change collation in sql 2005

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-06 : 01:18:23

how can i change the collation of the system database
if i create new database,so i get another defualt of collation?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 01:26:10

http://blogs.msdn.com/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

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

see them may be u get the answer...

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-06 : 10:20:14

i dont understand where i need to change.
i need,when i create new database my defualt will change, and all the system databse will cahnge too.(i need Latin_General_CI_AI)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 10:45:59

when you create new db just specify collation in collate clause


CREATE DATABASE dbname
ON
PRIMARY  
.....
COLLATE Latin_General_CI_AI
.......

http://msdn.microsoft.com/en-us/library/ms176061.aspx

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-06 : 11:54:41

thanks visakh16,i know that.

but i want to change the default,did i need to reinstall the SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 12:04:43

then you need to use ALTER DATABASE t-sql command
ALTER DATABASE.... COLLATE...

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-06 : 15:00:07

visakh16, i think that you dont understand me.

now the default of collation it is not what we use to.
i dont want change specific DB.
i want to change the defualt(when i create a new DB then i get the right collation and i dont need to ALTER)

thank a lot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 15:03:43

Then try doing it on the model database, not sure if that'll work but that's what the model database is there for (template for new databases).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-07 : 01:57:32

do you mean that:

ALTER DATABASE COLLATE Latin_General_CI_AI

will solve my problem?(next time when i crate a new DB my defualt collation is "Latin_General_CI_AI")

it is not dangerous to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 03:29:36

quote:
Originally posted by inbs

do you mean that:

ALTER DATABASE COLLATE Latin_General_CI_AI

will solve my problem?(next time when i crate a new DB my defualt collation is "Latin_General_CI_AI")

it is not dangerous to do it?

nope.. i thought what you were asking was to alter your current db's collation.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-07 : 05:20:49

so , i need to reinstall the sql ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:35:27

quote:
Originally posted by inbs

so , i need to reinstall the sql ?

for creation of new db with desired collation use COLLATE with CREATE DATABASE

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-07 : 14:35:59

thanks for try to help.but i know that,it is not solve my problem

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-07 : 14:37:27

So did you try doing it on the model database like I suggested? I don't know if it'll work as we always use the default collation, but you should at least give it a try in a test environment. The model database is used as a template for new databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-08 : 03:47:21

tkizer,i didnt try,i afraid to distroy something

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 04:05:16

That's why you need to do it in a test environment first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-08 : 06:54:45

this is the error message after i run:

ALTER DATABASE model COLLATE Latin_General_CI_AI

Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'model' because it is a system database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 07:02:03

I had a feeling it would be prevented. So if you want all new databases to get the new collation without having to manually specify it, then you'll need to reinstall SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources