locked
CRM2011: New Install SQL Collation Woes! RRS feed

  • Question

  • I'm installing CRM2011 onto a new Application Box with it's Databases going onto another dedicated SQL Server.

    The SQL Server has the sever collation set to:
    SQL_Latin1_General_CP1_CI_AS (our company default)

    Installing CRM asked for a collation for the default organisation:
    Latin1_General_CI_AS (only option that matched our company default)

    What we ended up with after installation was

    CompanyDB:                  Latin1_General_CI_AS
    MSCRM_CONFIG:           SQL_Latin1_General_CP1_CI_AS

    Q. Why does the CRM Installation or Deployment tool not understand SQL Collations?
    Q. Why has only one DB had the server default applied to it?
    Q. Is there a safe way of retro correcting the company database to embrace the server default, effectively resetting it back to SQL collation including all default data loaded into it during install?

    Many thanks

    Steve



    Thursday, February 21, 2013 5:21 PM

Answers

  • Taking your questions in turn:

    1. I expect that CRM may do some text operations (e.g. filtering, identifying duplicates, sorting) within its own code, rather than using SQL for all of this. If so, it makes sense to restrict the collation to Windows collations, so as to avoid any possible discrepancies between this and SQL operations. Then again, I believe these two collations are identical for Unicode, and as far as I'm aware, CRM uses unicode throughout for text data, so there shouldn't be discrepancies between these two collations
    2. Each companyDB can have its own collation, whereas I expect that CRM has no specific collation requirements for MSCRM_Config, so it just takes the server default for that database
    3. No, not completely. See point 1 above, and to change the existing collation would count as a direct SQL change, which is unsupported.

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by lemonje Wednesday, February 27, 2013 11:55 AM
    Wednesday, February 27, 2013 11:20 AM
    Moderator
  • Hi David

    Thanks for the reply. I was just about to answer my own question when I saw your post.

    After looking into this I've noticed the SQL_Latin1_General is referred to as a non-unicode collation. And as CRM uses unicode I guess that's why only windows collations are applicable.

    Now I know this I'm going to leave the collations alone. It's just a shame that all my databases have the same collation except the CRM DB. Lets just hope that it plays nicely with tempdb.

    Regards

    Steve


    • Edited by lemonje Wednesday, February 27, 2013 11:55 AM
    • Marked as answer by lemonje Wednesday, February 27, 2013 11:55 AM
    Wednesday, February 27, 2013 11:54 AM

All replies

  • Taking your questions in turn:

    1. I expect that CRM may do some text operations (e.g. filtering, identifying duplicates, sorting) within its own code, rather than using SQL for all of this. If so, it makes sense to restrict the collation to Windows collations, so as to avoid any possible discrepancies between this and SQL operations. Then again, I believe these two collations are identical for Unicode, and as far as I'm aware, CRM uses unicode throughout for text data, so there shouldn't be discrepancies between these two collations
    2. Each companyDB can have its own collation, whereas I expect that CRM has no specific collation requirements for MSCRM_Config, so it just takes the server default for that database
    3. No, not completely. See point 1 above, and to change the existing collation would count as a direct SQL change, which is unsupported.

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by lemonje Wednesday, February 27, 2013 11:55 AM
    Wednesday, February 27, 2013 11:20 AM
    Moderator
  • Hi David

    Thanks for the reply. I was just about to answer my own question when I saw your post.

    After looking into this I've noticed the SQL_Latin1_General is referred to as a non-unicode collation. And as CRM uses unicode I guess that's why only windows collations are applicable.

    Now I know this I'm going to leave the collations alone. It's just a shame that all my databases have the same collation except the CRM DB. Lets just hope that it plays nicely with tempdb.

    Regards

    Steve


    • Edited by lemonje Wednesday, February 27, 2013 11:55 AM
    • Marked as answer by lemonje Wednesday, February 27, 2013 11:55 AM
    Wednesday, February 27, 2013 11:54 AM
  • I would expect that any tables that SQL generates in tempdb will use the same collation as the source data, so I don't think that would cause a problem


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, February 27, 2013 1:46 PM
    Moderator