Missing SQL Collation
Question
Tuesday, June 30, 2015 5:22 PM
I have a SQL Server Management Studio, but I can't find the Collation Latin1_General_CI_AS in the drop down list
I need it in order work on an Australian client's issue. Could somebody tell me how to set the collation for a database if the collation is missing from the drop down list of collations?
All replies (9)
Wednesday, July 1, 2015 5:48 AM ✅Answered
I do want to change the collation of a database.
Hello,
The server as well as the database collation are only default value for DDL Statements without explicit defined collation setting; the effectiv collation is defined on column level. If the database + tables are already created with a different collation then you have to modify all tables/column definition.
If SQL Server / database are new, then have a look at:
Set or Change the Server Collation
Set or Change the Database Collation
Olaf Helper
Wednesday, July 1, 2015 6:35 AM ✅Answered
I do want to change the collation of a database. However that page is no good to me since it is using the GUI that is missing the collation I must set it to. I need to convert a
Hi Vitren,
If the following statement returns the correct result in query window, please use the T-SQL statement “ALTER DATABASE [DBname] COLLATE Latin1_General_CI_AS” which is mentioned in Olaf’s link instead of SQL Server Management Studio GUI to change the database collation.
SELECT * FROM fn_helpcollations() WHERE name = 'Latin1_General_CI_AS'
For more detailed steps to change the default collation of an existing database, please review the following blog.
A Seven-Step Process for Changing a Database's Collation
http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation
Thanks,
Lydia Zhang
Lydia Zhang
TechNet Community Support
Wednesday, July 1, 2015 6:43 AM ✅Answered
As Olaf mentioned, changing the database collation does not change collation for your existing tables (columns). For that you in addition to the ALTER DATABASE command also need to use ALTER TABLE ... ALTER COLUMN. The later for every table and column that you want to change collation for. And in order to do that, you need to remove indexes, keys, default and such - not a simple task if you want to do it for lots and lots of tables/columns.
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, June 30, 2015 7:03 PM
Try below:
SELECT * FROM fn_helpcollations() WHERE name = 'Latin1_General_CI_AS'
Do you see the collation? If you do, then there is just some problem in the GUI and you can use the COLLATE clause in the CREATE DATABASE or CREATE TABLE command (depending on the exact situation you have).
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, June 30, 2015 9:00 PM
Where do I put this? I don't see an editor or an IDE anywhere for the collation
Tuesday, June 30, 2015 9:54 PM
By your reply I infer that the query did indeed return one row. I.e., the collation is indeed available on the server and it is just the tool that is behaving weird.
So, what is it that you are doing? Are you creating a database? Chanding default collation for an existing database? Since the GUI doesn't work for you, you have to execute whatever underlying command in a query windows. For instance the CREATE DATABASE command and use the COLLATE clause to specify the desired collation.
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, June 30, 2015 11:33 PM
Changing the default collation of an existing database
Wednesday, July 1, 2015 12:01 AM
collation exists in two levels. in the server level and database level.
to see the server collation right click instance and choose properties. you will see the server collation.
any database created by default uses this collation.
If you want to change the database collation here is the link:
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database
Wednesday, July 1, 2015 12:23 AM
I do want to change the collation of a database. However that page is no good to me since it is using the GUI that is missing the collation I must set it to. I need to convert a
Latin1_General_CP1_CI_AS to Latin1_General_CI_AS
because the two have subtle nuances that must be accounted for in the bug I am handling