Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Changing MSDB Collation

    I support an applicatoin where the vendor requires the use of the LATIN_1_GENERAL_BIN sort collation in their user databases. Origionally, when I installed the instance, I did not select this sort collation for the instance. I have since gone back and reinstalled SQL Server with the correct sort collation. After that, I restored the vendors databases from backups I took prior to reinstalling the instnace and they retained the LATIN_1_GENERAL_BIN collation (the correct one).

    I also restored the MSDB database to get all of my jobs back. When I did this, MSDB took the old sort collation (SQL_LATIN_1_GENERAL_CPI_CI_AS). MSDB is the only database that has this collation and I'm getting errors in the agent log complaining of collation conflicts. I'm pretty sure the fact that the MSDB sort collation is different that all the other databases is the thing that's causing these error messages.

    My question is this: Is there any way to change the sort collation of MSDB without reinstalling SQL server? This is a clustered environment and everytime I've reloaded SQL, it's been cumbersome and taken me at least 1 day's work.

    Also, I know there's a rebuild master utility that will rebuild master, model and MSDB, but when I tried that last time it failed and I had to reinstall SQL anyway. Does anyone know of a way around this or have any tips on using the rebuildmaster utility in a clustered environment?

    Many thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've never tried this, and would STRONGLY urge you to make a backup before you try it.

    You can change the default colation for a database using ALTER DATABASE. You can use DBCC REINDEX to rebuild the indicies (to use the new collation).

    My gut feeling is that this should work, but let me stress again: Don't try this without at least two good backups!

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Thanks Pat, but I already tried this (after taking good backups) and it didn't work. SQL 2000 won't let you alter the collation of MSDB.

    I've been doing some researching and I actually think I'm going to try to use the rebuild master utility again. I've found an article in TechNet that gives better instructions than the ones I had before.

    Thanks for the input.


    Originally posted by Pat Phelan
    I've never tried this, and would STRONGLY urge you to make a backup before you try it.

    You can change the default colation for a database using ALTER DATABASE. You can use DBCC REINDEX to rebuild the indicies (to use the new collation).

    My gut feeling is that this should work, but let me stress again: Don't try this without at least two good backups!

    -PatP

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Rebuild master utility is the only tool availble to affect any changes after the installation.

    By default master's collation will be affected to remaining system databases.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See, I learned something today. Now I can go home!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •