Database Sort Order / Collation

Database Sort Order / Collation

• QAniket

  I've raised an issue in the public database for this so that it can be tracked. If anyone else believes that this issue warrants addressing, please visit the link below and vote on the 'suggestion':

  http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=78260115-1837-44d3-9def-ca8dcf8b159c

  
  
  

• averge joe

  Laxmi, thanks for replying.
  
  Looking at the CREATE DATABASE syntax, there is no option for specifying an LCID:
  
  

  CREATE DATABASE database_name 
    [DATABASEPASSWORD 'database_password' 
     [ENCRYPTION {ON|OFF}] 
    ]
    [COLLATE collation_name comparison_style] 
  database password ::= identifier

  Nor is there an option on the Management Studio Create Database dialog to specify an LCID (just the collation_name again).
  
  I assume you mean in code, as such:
  
  

  System.IO.File.Delete("Test.sdf");
  string connString = "Data Source='Test.sdf'; LCID=3076; Password=\"s$;2'!dS64\"; Encrypt = TRUE;";
  SqlCeEngine engine = new SqlCeEngine(connString);
  engine.CreateDatabase();
  

  I did not try this, because I am now able to create the database from the dialog (I've no idea why, but all of the Asian Collations suddenly appeared - I haven't installed anything special recently...), but why do I (still) get an error when I try to use Mobile TSQL
  
  Are you able to answer my previous questions
  
  Again, thanks for your time.
  
  
  
  

• DanMoyer

  Thanks. Please post here when you find out! Cheers.
  
  

• MissyKos

  SQL Mobile supported colations are available in SQL Mobile Books Online @ http://msdn2.microsoft.com/en-us/library/ms174596(en-US,SQL.90).aspx

  Thanks,

  Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• Matt Vandergrift

  Thanks for the response. I have reviewed the link, and it indeed lists all 'possible' SQL Mobile collations. However, it also indicates "Some collation values might not be supported on some devices."
  
  As mentioned in my post, I was able to create a database using the Chinese_Hong_Kong_Stroke_90_CI_AS collation, and i was able to synchronise this with a local database using replication. I can't however, find a way in the SQL Management Studio to view the Collation property of a database.

  1) How is it possible to check the collation of an existing SQL Mobile database using either (a) Mobile TSQL code, or (b) SQL Management Studio

  In the example on the link that you kindly referred to, there was an example listed, using a Spanish collation. I tried the example on my machine, and the database was created successfully:

  CREATE DATABASE "c:\temp\SpanishDB.sdf" DATABASEPASSWORD '1234' COLLATE Traditional_Spanish_CI_AS

  Command(s) completed successfully.

  However, when I use the locale that I want to use, Chinese_Hong_Kong_Stroke_90_CI_AS, it fails:

  CREATE DATABASE "c:\temp\ChineseTestDB.sdf" DATABASEPASSWORD '1234' COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS

  Major Error 0x80040E14, Minor Error 25033

  > CREATE DATABASE "c:\temp\ChineseTestDB.sdf" DATABASEPASSWORD '1234' COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS

  The specified locale is not supported on this operating system.

  This is actually the original issue. The Connect/Create SQL Mobile database dialog actually disallows me from choosing this locale (ie., the Windows System locale "Chinese (Hong Kong)") didn't exist when I tried to create a new SQL Mobile database, although another check reveals a new list of options.

  I am using Windows XP Professional, SP2. The computer was originally upgraded from Windows 2000 Pro (I'm not sure whether this is relevant).

  2) Why do I get the above error (The specified locale is not supported on this operating system.), and where is the documentation that indicates which locales are supported on which operating systems

  3) What can I do to rectify this issue - ie. use SQL Mobile TSQL to create a database using Chinese_Hong_Kong_Stroke_90_CI_AS

  Thanks in advance for any feedback/input received. Cheers.

  
  
  

• Jerry Goodwin

  SQL Mobile collation is database level and can not be set for a table separately. And hence your 2nd adn 3rd questions are not valid for SQL Mobile DB. For the first question, I need to find out whether there is any way to find out SQL Mobile DB collation.

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• Joseph Geretz

  Yes you are right that Query Processing does not have LCID. The only way to do is through the program.

  As you said the following command always fails for me:

  CREATE DATABASE "c:\temp\ChineseTestDB.sdf" DATABASEPASSWORD '1234' COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS

  I am still digging into it why with a collation name I can not create a DB though it is supported!

  Also, I could never get this collation not supported problem with New Database wizard of SQL Server Management Studio. Can you let me know how to repro that too

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• Achenar

  Can you please try creating the database by giving a LCID 0x0c04.

  LCID way is working and we also find that Chinese_Hong_Kong_Stroke_90_CI_AS is not recognized.

  Thanks,

  Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• Muhammad Adeel Malik

  Re: Also, I could never get this collation not supported problem with New Database wizard of SQL Server Management Studio. Can you let me know how to repro that too

  I initially said that the option to select "Chinese - Hong Kong SAR " from the "Create new SQL Server 2005 Mobile Edition Database" was not available - as mentioned, the best (closest) choice I had available was a "General", however this has disappeared in lieu of the Asian regional collations (I know not why!).

  I am now able to create a database using this method. Further, I was previously able to create a database with this collation as a SQL Server 2005 databse (ie. not a Mobile Database). I then followed all of the steps for replication, and it (eventually) worked.

  Please let me know how to (if you know):

  1. check the collation for a SQL Mobile database
  2. set the collation for a new table in SQL Mobile
  3. check the collation for given table/column in SQL Mobile

  Thanks.

  
  
  

• Robert Rossney

  Hi,

  We do not have any public API with which you can find the collation of a SQL Mobile Database. This is very sad news but true. We would take this as feedback and would definitely escalate and make sure that it is being considered for the next release (may be next version of the product or a service pack).

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• FrenchStudent

  Thanks a lot.

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  
• Database Sort Order / Collation