none
collation

    Question

  • there are tons of questions about collation...

    I read some and found useful info...

    New SQL server 2012 (English) is installed on Server 2012 (English).

    Server Collation is : Latin1_General_CI_AS

    New app version will be installed on dedicated APP server that will use the instance of above described SQL.

    I still don't know if the app will be in French.

    1. is there a chance that the app written in French will not function correctly with Latin1 collation. It is not just about French UserGUI from Web app. Users enter data in French.

    2. a small existant DB for the same app (old version) should be transferred from old SQL on which collation is set to French_CI_AS to a new one with Latin1 collation.

    What to expect?

    3. Could a collation be set on DB basis? In case that different apps require different collation...

    Can somebody elaborate real life examples.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Wednesday, February 15, 2017 12:40 PM
    Tuesday, February 14, 2017 7:31 PM

Answers

  • really encouraging answer :). I will talk tomorrow to the vendor. He should know it.

    This first App is Web app. So users will access it by browser.

    The old version is not web based. 

    I asked a general question what will happen with DBs that will be copied to the server with different instance collation.

    French is a latin-based language

    But you know that it has multiple accents...

    May be somebody with similar to mine environment can confirm.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    Yes, but in both cases (old db server and new) you have case insensitivity and accent sensitivity set at the instance level.

    Joie Andrew "Since 1982"

    • Marked as answer by pob579 Wednesday, February 15, 2017 12:27 PM
    Wednesday, February 15, 2017 5:51 AM
  • Hi,

    Yes, you can set the collation on a DB basis, but that may bring issues when you query against temporary tables (tempdb) which will be set according to instance default collation.

    • Marked as answer by pob579 Wednesday, February 15, 2017 2:28 PM
    Wednesday, February 15, 2017 2:24 PM

All replies

  • Not sure how this would be a problem French is a latin-based language. It should be served by that collation fine. Unless you know the app has different needs (like case-sensitivity).

    Are you going to be doing any user acceptance testing or QA? This should help verify this.


    Joie Andrew "Since 1982"

    Tuesday, February 14, 2017 8:19 PM
  • really encouraging answer :). I will talk tomorrow to the vendor. He should know it.

    This first App is Web app. So users will access it by browser.

    The old version is not web based. 

    I asked a general question what will happen with DBs that will be copied to the server with different instance collation.

    French is a latin-based language

    But you know that it has multiple accents...

    May be somebody with similar to mine environment can confirm.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 11:58 PM
  • Hi,

    Latin1_General_CI_AS is perfectly suitable for both English and French.

    I use it here (Montréal, Québec, Canada) on a daily basis in both languages

    Wednesday, February 15, 2017 2:54 AM
  • You have to be careful when you have databases or tables with different collations and have to do cross database queries.

    You may also run into problems when tempdb is a different collation than your database. You may get conflicts with the error message:

    Cannot resolve collation conflict for equal to operation

    Wednesday, February 15, 2017 3:30 AM
  • really encouraging answer :). I will talk tomorrow to the vendor. He should know it.

    This first App is Web app. So users will access it by browser.

    The old version is not web based. 

    I asked a general question what will happen with DBs that will be copied to the server with different instance collation.

    French is a latin-based language

    But you know that it has multiple accents...

    May be somebody with similar to mine environment can confirm.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    Yes, but in both cases (old db server and new) you have case insensitivity and accent sensitivity set at the instance level.

    Joie Andrew "Since 1982"

    • Marked as answer by pob579 Wednesday, February 15, 2017 12:27 PM
    Wednesday, February 15, 2017 5:51 AM
  • Thanks to all.

    I feel there should not be any problem. The app install is planned for the next  week and there is no pressure. And tests will be made after merging new and old DBs.

    So in case of a problem I will ask having some errors or other indications...

    Please answer the question # 3 from initial post:

    3. Could a collation be set on DB basis? In case that different apps require different collation...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Wednesday, February 15, 2017 12:32 PM
    Wednesday, February 15, 2017 12:27 PM
  • Hi,

    Yes, you can set the collation on a DB basis, but that may bring issues when you query against temporary tables (tempdb) which will be set according to instance default collation.

    • Marked as answer by pob579 Wednesday, February 15, 2017 2:28 PM
    Wednesday, February 15, 2017 2:24 PM
  • Thanks Seb! 

    Good to know...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Wednesday, February 15, 2017 2:29 PM
    Wednesday, February 15, 2017 2:27 PM
  • checked collation on multiple DBs in one instance.

    There are different collations including Latin1_General_CI_AS and the instance is set to French_CI_AS.

    The first DB  that should be merged with a new DB on a new SQL is Latin1_General_CI_AS (as the instance).

    So should not be any problem Collation wise.

    From my "findings" on the old server, can I assume that before merging DBs it would be necessary/right to create a new DB on a destination server with the collation of the source DB?

     


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 16, 2017 2:45 PM