Forum Discussion

Ronen_Tzimbel's avatar
Ronen_Tzimbel
Level 6
14 years ago
Solved

Sql Collation

Hello,

I move the Sql Database from sql server 2000 to sql server 2005.

When I run the Deployment Scanner,the follow issuse shows up:

Information:no mixed collation found in Enterprise Vault SQL Database

sqlsrv: Unmatched default SQL collation: Server: Hebrew_CI_AS ; EnterpriseVaultDirectory database: SQL_Latin1_General_CP1_CI_AS.
sqlsrv: Database: EnterpriseVaultDirectory - no mixed collations found.
sqlsrv: Unmatched default SQL collation: Server: Hebrew_CI_AS; EnterpriseVaultMonitoring database: SQL_Latin1_General_CP1_CI_AS.
sqlsrv: : Database: EnterpriseVaultMonitoring - no mixed collations found.
sqlsrv:  Unmatched default SQL collation: Server: Hebrew_CI_AS; EVEVStore01 database: SQL_Latin1_General_CP1_CI_AS.
sqlsrv: : Database: EVEVStore01 - no mixed collations found.

Seems that the collation settings are different set for the server than the actual EV databases.

Is it needed to change the collation settings for the EV databases to the version of the server i.e. Maste database?

I attach a screenshot for the Deployment Scanner

Would the upgrade failed when actually running it?
is it recommended to using with article:
http://www.symantec.com/business/support/index?page=content&id=TECH55063

Many Thanks Ronen

Enterprise Vault
Information Governance
  • JesusWept3's avatar
    JesusWept3
    14 years ago

    I would suggest running this script instead because i agree, the technote is pretty bad and hard to follow

    http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

8 Replies

Sort By
  • RahulG's avatar
    RahulG
    Level 6
    14 years ago

    The upgrade might get successful but Its better you fix this issue and then proceed with the upgrade .

  • Ronen_Tzimbel's avatar
    Ronen_Tzimbel
    Level 6
    14 years ago

    Hello,

    but the procedure to fix that is very confused.

    and need to Dba admin.

    Thanks Ronen

  • JesusWept3's avatar
    JesusWept3
    Level 6
    14 years ago

    I would suggest running this script instead because i agree, the technote is pretty bad and hard to follow

    http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

  • GertjanA's avatar
    GertjanA
    Moderator
    14 years ago

    Hi Ronen,

    Script JW points you to is good. Make sure to have good backup's of the databases!

    Also, run the script on 1 DB at the time. It uses the temp-table, and if you run it to more, it gets confused, and messes up your databases (been there, done that)

    If you run it 1 database at the time, it will succeed.

  • MarkBarefoot's avatar
    MarkBarefoot
    Level 6
    14 years ago

    that is actually referred to in the collation technote, in the SQL2005 section. I would also advise on checking the SQL output window on completion, as it reports "completed" but it can log issues higher up in the output log.

    Believe me, you don't want to get into collation issues as they can get nasty. If the collation script doesn't work properly, you could end up with databases that have no indexes, keys, constraints etc. BACKUP everything before hand :)

     

    Also read the know issues bit at the bottom of the TN, it mentions about the tmp table stuff etc

  • FreKac2's avatar
    FreKac2
    Level 6
    14 years ago

    And while we are at it, if you're on SQL 2008 you need to change the script as the last comment states.

    So that it can handle varchar(max) correctly.

    Otherwise you'll get a non-working DB afterwards.

    Been there done that but had backups.....of course :)

     

  • Jeff_Shotton's avatar
    Jeff_Shotton
    Level 6
    14 years ago

    All that is wrong here is that the server collation is different from the Enterprise Vault databases. Ergo all you need to do is change the master database collation, rather than ALL the other databases.

    http://msdn.microsoft.com/en-us/library/ms179254%28v=SQL.90%29.aspx

    Change the master database to SQL_Latin1_General_CP1_CI_AS

    Yes, it involves detatching all the current EV databases - but you just did this as part of the SQL move, so you should know what you are doing there.

    I am also assuming of course that you are only running Enterprise Vault databases on this server.

    Regards,

    Jeff

  • MarkBarefoot's avatar
    MarkBarefoot
    Level 6
    14 years ago

    As Jeff states, only do this if your are only hosting EV on the SQL server, else other things may break :(