how can change collation of databas

[hanyniazy]

i want to change collection of DB

 

[hmckillop]

have a look at this, it should do the job for you.

http://www.leyton.org/techiemusings/2004/08/12/sql-server-and-collation-settings

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hanyniazy]

dear mr
thank's for answer but i have one database want to change it only

 

[hmckillop]

This post explains how to change the collation of a database.
The collation is specified as part of the configuration of SQL server, not at a database level.
SQL collation settings correspond to the type of installation.In general, choose a SQL collation that supports the Windows locale most commonly used at your site. For more information about identifying your site Windows Locale, see Regional Settings in Windows Control Panel. In many cases, a computer will run the Windows locale that matches the language requirements of the user, so Setup automatically detects the Windows locale and chooses the appropriate SQL collation


"I'm living so far beyond my income that we may almost be said to be living apart

 

[yelworcm]

SQL Server 2000 allows collation changes down to the column level. Why you would want to do this, I am not sure.

To change the default collation of a database, you can use the alter database command. This will only change the default collation for the system catalog, and future user objects, though.

In order to change the collation of each and every char, varchar, nchar, nvarchar, text and ntext column already existing in the database, you will have to generate/write a script that issues alter table ___ alter column to the appropriate settings.

One warning, though, if your code joins character data to any temp table, or table variable, you are going to be in for a world of hurt, as tempdb will always have the server default collation, so error 446: "Cannot resolve collation conflict for %ls operation" will be assured. As always, use your test server first, before the users start airing theories about the dubiousness of your parentage.

 

[mrdenny]

In SQL 2000 you can select the colation at the Server level, Database level, and column level. You can change the default colation of your database with the alter database command.

alter database [i]Database[/i]
collate [i]Collation[/i]

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hanyniazy]

thank's alote for this script


and i have anther 2 quetion

first one who i can used this script to change all field in database with database?

secand

i have data arabic in this database and when i change coleation i still can't reading arabic data put when i insert new this work what can i do to reading old data after i change collecion and all fiald in database?

 

[mrdenny]

1. You can't. Any objects that you specified the collation type for will need to be changed. This can probably be scripted via some fancy programming.

2. What data type are you using to store the Arabic?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hanyniazy]

datatype is using to store arabic CHAR
and i can't change it to any anther datatype
and ur script is work but it didn't change old store data
i want change it only ?


and aout collection do u have any script to do that or do u have script return all object in database and collection of it to used it to change colletion

 

[mrdenny]

In order to store the Arabic data you need to store it in a field with the datatype of NCHAR. The CHAR datatype will not correctly store characters which are not in the latin character set (English, Spanish, French, etc).

If your application is limited to using the CHAR data type then you will not be able to use Arabic data. I would recommend contacting the vendor of the application for support.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com