August 4, 2011 at 9:21 pm
I tried to change database collation settings changes.
it shows query successfully ran. But database is not changing language..I changed collation to Arabic...but its still showing in English.... what to do??? I tried in many ways....
Sagar Sonawane
** Every DBA has his day!!:cool:
August 4, 2011 at 10:51 pm
Alter Database mirrordb Collate Arabic_100_CS_AI_WS
After running this query still its sowing in default collation...
I just want to change it for a single database...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 5, 2011 at 1:29 am
Changing the database collation does not change the collation on all the table columns in the database.
You could move the database to a new server with the same collation and then change the server's collation with the method described here[/url].
Once finished, you can move the database to its original location.
Hope this helps
Gianluca
-- Gianluca Sartori
August 5, 2011 at 1:34 am
I got task to Collate database. within same server.
I used this Query
Alter database DBname collate "collation_name"..
after executing it was showing Executed Successfully.
But there was no result ...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 5, 2011 at 2:02 am
Saga... (8/5/2011)
I got task to Collate database. within same server.I used this Query
Alter database DBname collate "collation_name"..
after executing it was showing Executed Successfully.
But there was no result ...
If with "no result" you mean that the collation on all the columns in the database was not altered, this is by design.
Let me quote myself:
Gianluca Sartori (8/5/2011)
Changing the database collation does not change the collation on all the table columns in the database.
Using another server to change the collation is just a "trick" to achieve something that cannot be done otherwise.
-- Gianluca Sartori
August 5, 2011 at 2:11 am
The change in database collation will only affect objects created after the change. As per BOL:
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
August 5, 2011 at 2:56 am
Cath Trimble (8/5/2011)
The change in database collation will only affect objects created after the change. As per BOL:You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
I collate database, then tables inside it, & columns related to those columns.
I tried to make some new insertion as well, in the database. but nothing reflected.
Sagar Sonawane
** Every DBA has his day!!:cool:
August 5, 2011 at 3:00 am
Cath Trimble (8/5/2011)
The change in database collation will only affect objects created after the change. As per BOL:You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
I just have a standalone server :(..... second thing I did collate tables & columns as well..
Sagar Sonawane
** Every DBA has his day!!:cool:
August 5, 2011 at 3:45 am
I'm sorry, I think I may have misread your original post. When you say 'the database is not changing language ... it's still showing in English' are you expecting the collation change to change the language of the data in the tables from English to Arabic? Collation affects the code page used to store non-Unicode data and how data are sorted and compared, not the language data are stored in.
August 12, 2011 at 12:21 am
I am really confused about my terminology about collation.
Please any one can explain in very simple terms "What is collation"?
Sagar Sonawane
** Every DBA has his day!!:cool:
August 12, 2011 at 2:59 am
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).
Each SQL Server collation specifies three properties:
The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
The sort order to use for non-Unicode character data types (char, varchar, and text).
The code page used to store non-Unicode character data.
To resolve this issue i would;
On the same server create a new database dbname_NEW with the correct collation.
Script all the objects from the existing database and run the script against the new database (powershell / SSIS / generate scripts wizard).
Copy the data from the old database to the new database.
Rename the current database dbname_OLD.
Rename the new database dbname.
I would be wary of changing between collations though there may be some characters that exist under the current collation that are not supported under the new collation.
Thanks
Chris
August 12, 2011 at 3:17 am
msdn: Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, an English speaker would expect the character string 'Chiapas' to come before 'Colima' in ascending order. However, a Spanish speaker in Mexico might expect words beginning with 'Ch' to appear at the end of a list of words starting with 'C'. Collations dictate these kinds of sorting and comparison rules.
http://msdn.microsoft.com/en-us/library/ms187582.aspx (Working with Collations)
Collations don't change the language data are stored in. The above link gives clear explanations and links to other resources.
August 12, 2011 at 3:19 am
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).
Each SQL Server collation specifies three properties:
The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
The sort order to use for non-Unicode character data types (char, varchar, and text).
The code page used to store non-Unicode character data.
To resolve this issue i would;
On the same server create a new database dbname_NEW with the correct collation.
Script all the objects from the existing database and run the script against the new database (powershell / SSIS / generate scripts wizard).
Copy the data from the old database to the new database.
Rename the current database dbname_OLD.
Rename the new database dbname.
I would be wary of changing between collations though there may be some characters that exist under the current collation that are not supported under the new collation.
Thanks
Chris
August 12, 2011 at 5:14 am
Thank You Chris, Cath.....
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply