Forum Discussion

GertjanA's avatar
GertjanA
Moderator
14 years ago
Solved

Change collation SQL2005 EV9

Hello!

Does anyone have a working (and preferably easy ;-) )script that changes the collation for EV databases?

SQL2005SP3, EV9SP1 databases.

I'm moving from 1 SQL server to another, and unfortunately, the collation does not match on the servers.

There are already databases (other apps) on the new server, so it is no option to re-install SQL....

I've tried following the steps in the documentation, using the MS sql, but I cannot get that to work. I am not a DBA (unfortunately), and need to change this.

Symantec used to have a script for SQL2005, where did that go?

Enterprise Vault
Error messages
Information Governance
Tip-How to
Troubleshooting
Upgrade
  • JesusWept3's avatar
    JesusWept3
    14 years ago

    OK Ignore the symantec article , its hideously overworded and doesn't need to be that difficult

    So do the following!

    1. Download the script here:
    http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-01-19-58-59/change_5F00_collation.sql

    2. Open it in SQL Management Studio (2005 we'll use for this)

    3. From the menu at the top, select "Query" and then  you will have an option called "SQLCMD Mode", click this

    4. Next lets concentrate on these lines at the very top , its all you need to look at

    :SETVAR destdb IOFFICE2000
    :SETVAR desired_collation Cyrillic_General_CI_AS
    :SETVAR script_only 0

    So the first line is "IOFFICE2000", we'd just change this to be "EnterpriseVaultDirectory"
    The Second line is the collation you want to change to, so go to the properties of your Master DB

    1. In SQL Management Studio, expand out Databases ->  System Databases
    2. Right click "Master" and select "Properties"
    3. Copy the Collation from the "Collation" column

    Change the "desired_Collation" in the script  to be what you just copied

    script_only 0 = means it will make the changes
    script_only 1 = means it runs in a report mode, where it just shows you what it would do, but not do anything

    So now the top part should look like this

    :SETVAR destdb EnterpriseVaultDirectory
    :SETVAR desired_collation SQL_Latin1_General_CP1_CI_AS
    :SETVAR script_only 0

    Obviously what i would suggest is to stop all the services (including DA etc) and to also make your backups so you can fall back should anything go wrong

    And thats it! hit execute and let it finish!
    Then just go through one by one

    SETVAR destdb EnterpriseVaultMonitoring
    ... execute that

    SETVAR destdb EnterpriseVaultAudit
    ...execute that

    you'll be done soon enough :)
     

6 Replies

Sort By
  • GertjanA's avatar
    GertjanA
    Moderator
    14 years ago

    Hi JW, I've tried using this, but find descirption on sym-site stressy to say the least.

    Do yo happen to have some steps how to do this.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    14 years ago

    OK Ignore the symantec article , its hideously overworded and doesn't need to be that difficult

    So do the following!

    1. Download the script here:
    http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-01-19-58-59/change_5F00_collation.sql

    2. Open it in SQL Management Studio (2005 we'll use for this)

    3. From the menu at the top, select "Query" and then  you will have an option called "SQLCMD Mode", click this

    4. Next lets concentrate on these lines at the very top , its all you need to look at

    :SETVAR destdb IOFFICE2000
    :SETVAR desired_collation Cyrillic_General_CI_AS
    :SETVAR script_only 0

    So the first line is "IOFFICE2000", we'd just change this to be "EnterpriseVaultDirectory"
    The Second line is the collation you want to change to, so go to the properties of your Master DB

    1. In SQL Management Studio, expand out Databases ->  System Databases
    2. Right click "Master" and select "Properties"
    3. Copy the Collation from the "Collation" column

    Change the "desired_Collation" in the script  to be what you just copied

    script_only 0 = means it will make the changes
    script_only 1 = means it runs in a report mode, where it just shows you what it would do, but not do anything

    So now the top part should look like this

    :SETVAR destdb EnterpriseVaultDirectory
    :SETVAR desired_collation SQL_Latin1_General_CP1_CI_AS
    :SETVAR script_only 0

    Obviously what i would suggest is to stop all the services (including DA etc) and to also make your backups so you can fall back should anything go wrong

    And thats it! hit execute and let it finish!
    Then just go through one by one

    SETVAR destdb EnterpriseVaultMonitoring
    ... execute that

    SETVAR destdb EnterpriseVaultAudit
    ...execute that

    you'll be done soon enough :)
     

  • GertjanA's avatar
    GertjanA
    Moderator
    14 years ago

    Hi JW,

    That should be easy indeed.

    Last question. This will only change the collation of the EV-databases?

    I'm asking because the EV-databases are being moved to a server that has more databases already.

    In other words, I am not changing mast, msdb or any other system related database right?

    This is a 'I need to be sure' question.

    Thanks for the thorough explanation. My customer is on SQL2005 also, so should be easy.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    14 years ago

    understood :)
    It will ONLY change the database that you specify in :SETVAR destdb EnterpriseVaultDirectory
    If you were to set that to destdb Northwind, it would change the Northwind database

     

  • GertjanA's avatar
    GertjanA
    Moderator
    14 years ago

    Thanks again.

    I've sent the above to the customer, with the link to this article. I also translated a little in dutch. I'll let you know if it worked for the customer. I don't understand why Symantec makes it so difficult, with all those drops etc..