locked
How to change SQL server 2008 collate after installation RRS feed

  • Question

  • Hi,
     
    On windows sever 2008 I've installed SQL server 2008 RC0.

    At the time of installation, I'd specified the collation as LATIN1_GENERAL_BIN, and now I want to change this collation to LATIN1_GENERAL_CI_AS.

    Please note that I don't want to change the collation of specific db but need to change the sql server 2008 server's settings.

    How to accomplish this requirement with minimum efforts?

    Thanks
    Tuesday, July 29, 2008 8:51 PM

Answers

All replies

  • I don't think it's possible - collation settings at setup determine system database collations, which can't be changed after installation (at least in 2005, and it doesn't seem to have changed in 2008). You'll need to reinstall the instance (or add another instance) with the desired server collation.
    • Proposed as answer by VidhyaSagar Friday, December 12, 2008 7:13 AM
    Wednesday, July 30, 2008 12:03 AM
  • Yes you can but it requires rebuilding master and reloading your databases. You'd have to do this from the command prompt with something like

     

    Code Snippet
    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

     

     

     

    This is for 2005. BOL documents this in detail under the How to: Install SQL Server 2005 from the Command Prompt or the section. Just go there and search for Specifying a New System Collation.

     

    2008 is very similar. See Setting and Changing the Server Collation or Setting and Changing the Database Collation in BOL depending on which you're trying to accomplish.

     

     

    joe.

    Wednesday, July 30, 2008 7:13 PM
  • Hello Joe,

     

    Thanks for your help.

     

    I tried with the command you given but still getting the problem.

     

    Details:

     

    start /wait Z:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=MyPassword211 SQLCOLLATION=Latin1_General_CI_AS

     

    Here, Z:\ is a map drive which contains sql server 2008 setup files.

     

    It shows me following error:


    The Syntax of argument "/QN" is incorrect. Either the delimiter '=' is missing or there is one or more space characters befor the delimiter '='.

     

     

    Thanks

    Tuesday, August 5, 2008 12:56 PM
  • According to the link that Joe posted, the switch should be /QB, not /QN.
    Wednesday, August 6, 2008 7:53 PM
  • I also tried with /QB, but with this switch it's showing me same problem.

     

     

    Wednesday, August 6, 2008 8:06 PM
  • I hope /QB parameter no more exits in SQL 2008 for silent installation, you need to use /Q parameter for the same.

     

    Thursday, August 7, 2008 10:56 AM
  • I tried /Q option too, but it was also not working.

    Saturday, August 9, 2008 12:21 PM
  • Have you managed to get this to work with the RTM version? AFAIK, changing collation settings post install was not available until after RC0.

     

     

    joe.

    Thursday, August 28, 2008 4:08 PM
  • See the following blog with the correct syntax for rebuilding system databases in SQL Server 2008.  Also covers the syntax for changing the server collation.

     

    http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

    Monday, September 8, 2008 9:20 PM
  • i uninstalled sqlexpress 2008 and re-installed it again to change the collation to Latin1_General_CS_AS, but how must now change my actual DATABASE's collation. i must rebuild it yes, but how do i do that??? i need to use setup.exe , i only have: SQLEXPR_x86_ENU. this is the application i used to install sqlexpress 2008...i got this application off microsoft site...

     

    thank you

    helloise

    Friday, September 19, 2008 9:55 AM
  • i rebuilt my db with a different collation. when i check the summary log it says collation is Latin1_General_CS_AS(correct), but when i open my db and check the field collation of a table it is still the old one?? how does that work i dont understand. can someone please xplain?

     

    thanks

    helloise

    Monday, September 29, 2008 9:07 AM
  • Yes, its possible. Either you've done one of two things.

    1) Took the backup of database which was having old collation and restore it on database with new collation.

    2) Detached the database on old collation and attached on database with new collation.

     

    In either of case, the table field collation will show old collation.

     

    I had same problem, I fixed it with the following ways.

     

    1) Create a empty database on new collation

    2) Restored the back up of old collation on new collation with diff database name

    3) Import table data from old to new one, making use of Import/Export data wizard.

     

    In this way what will happen is when you create an empty database, it will have new collation and when you import table data it will have new collation as well.

     

    Hope this helps

     

    C

    Wednesday, December 10, 2008 4:27 PM
  • Hi,

    you can change the collation by using the MS SQL Server 2008 Management Studio too.
    This would be the corresponding command, which runs -quiet- for a comparingly long time (4min) since the DOS Window is closed:

    E:\setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=x \Administrator /SAPWD=x /SQLCOLLATION=SQL_Latin1_General_CP850_BIN2

    After that you can find the log here:
    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt


    Here is a screenshot:


    Axel Arnold Bangert - Herzogenrath 2009
    Tuesday, December 29, 2009 5:13 PM
  • i rebuilt my db with a different collation. when i check the summary log it says collation is Latin1_General_CS_AS(correct), but when i open my db and check the field collation of a table it is still the old one?? how does that work i dont understand. can someone please xplain?

    helloise

    SQL Server and database collations are DEFAULT collations. Actual collations in tables maybe quite different.

    A shocker: COLLATION is not table-level, rather column-level property. Therefore, text columns in a table must individually be rebuilt to the desired collation.

    Work around: just place COLLATE DATABASE_DEFAULT to the right side of an expression giving collation error.

    Related articles:

    http://www.sqlusa.com/bestpractices/changecollation/



    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016





    • Edited by Kalman Toth Tuesday, November 14, 2017 6:45 PM
    Saturday, January 9, 2010 7:11 PM
  • Hi,

    I have always used the below command. This has to be done after inserting the setup disk. Hope this works for you.

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
    /SQLCOLLATION=CollationName

    Thanks
    Rohit
    Monday, February 1, 2010 10:21 AM
  • The above will work, but it's important to keep checking the log file:

    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

    As you may have to overcome any errors in there before it will let you apply the change. If you're trying to apply this change to a cluster, good luck, because i haven't been able to do it yet! ...Looking like a reinstall!
    Friday, February 19, 2010 3:23 PM
  • Hi Friends,

    It was a very healthy discussion. I would like to know the exact soultion to fix this issue.

    can anyone post the answer?

     

    Thanks,

    Ayyappan

    Wednesday, August 25, 2010 2:04 PM
  • It should be this one:

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
    /SQLCOLLATION=CollationName
    
    

     

    However this is true only for microsoft SQL server 2008. The express version has a lot of bugs (varying from automaticly choosing collation according to language or regional option (depends on what OS), or a major bug in bootstrapped setup.exe or even on new installer which does not allow direct access to setup.exe).

    There are a several workarounds (But these do not help you to make your DB clean and neat):

    1. change the collation of the database, and not the collation of the server.

    2. install SQL server express 2005 WHICH ALLOWS YOU TO CHOOSE COLLATION IN THE INSTALLER! (jesus... what were they thinking in microsoft when they released MSSQLEXPRESS 2008?)

    Tuesday, September 14, 2010 7:51 AM
  • hi, i had a similar challenge and not wanting to re-install the instnace, i looked around the management studio control and found the "FACETS" in the sql 2008 express. Do this by right-clicking on the db in question and select FACETS...You will find a editable field for Collation. There isn't a drop down list of all the types, so you wil have to know the name in advnaced.

    hope this helps...

     

    good luck,

    Thursday, September 16, 2010 1:03 AM
  • 1. change the collation of the database, and not the collation of the server.

    It should be noted that the references are to SQL Server default collation and database default collation. Actual column collations will not change by changing the defaults. You have to perform ALTER TABLE... ALTER COLUMN... to change column collation. Alternative is rebuilding (CREATE TABLE & populate) the table with the desired collation. See articles in my previous post.


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Edited by Kalman Toth Tuesday, November 14, 2017 6:45 PM
    Friday, October 15, 2010 1:08 PM
  • will it work in cluster also ?? or we having some different way to implement in Cluster Env. 
    india
    Friday, July 22, 2011 7:10 AM
  • For cluster environemnt you just need to take the resource(SQL server service) offline from cluster admin and follow the same process. Check this one.

    http://www.sqlservercentral.com/articles/Collation/70685/

    • Proposed as answer by MSSQL DBA Thursday, August 4, 2011 8:39 AM
    • Marked as answer by Olaf HelperMVP Sunday, April 7, 2013 6:15 PM
    Tuesday, August 2, 2011 10:15 PM
  • It works but it destroyed all my DB ... :/
    Tuesday, January 22, 2013 11:19 PM
  • It rebuilds all the system database, so you will loose any data stored in the system databases like logins, linked servers, jobs, etc. You need to attach your user databases so that you could use them.

    Tuesday, January 22, 2013 11:26 PM
  • It works but it destroyed all my DB ... :/

    Hello Julien,

    Can you explain more detailed, what happens, please?

    As V. Keerthi Deep wrote before, all attached databases + server logins are stored in the "master" database, so when you rebuild the system database, those informations get lost. You have to attach all before existing databases manually and you have also add all logins again.

    See TechNet Attach a Database and Create a Login


    Olaf Helper

    Blog Xing

    Wednesday, January 23, 2013 4:47 AM
  • At the time of installation, I'd specified the collation as LATIN1_GENERAL_BIN, and now I want to change this collation to LATIN1_GENERAL_CI_AS.

    Please note that I don't want to change the collation of specific db but need to change the sql server 2008 server's settings.

    How to accomplish this requirement with minimum efforts?

    Hi there. I realize this is a rather old question, but for anyone looking for info on changing the collation of a SQL Server instance (especially after it's been in use for any amount of time), please see the following post of mine that provides a detailed description of the undocumented "sqlservr.exe -q" option:

    Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

    That approach is definitely less effort than rebuilding and reconfiguring the instance. To leave one DB unmodified, just detach it first, do the update, then re-attach it (this is also noted in the post itself).

    Take care,
    Solomon...

    _____________________________________________________________
    SQL# https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap https://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR
    _____________________________________________________________


    Friday, May 8, 2020 6:58 AM
  • A shocker: COLLATION is not table-level, rather column-level property. Therefore, text columns in a table must individually be rebuilt to the desired collation.

    Work around: just place COLLATE DATABASE_DEFAULT to the right side of an expression giving collation error.

    Hello Kalman. The DATABASE_DEFAULT option is not a universal fix for collation errors. It is intended mainly to resolve issues in temporary tables when:

    1. the database the code is executing in has a default collation that is different than the instance-level collation, and
    2. the database's default collation was used in permanent tables and/or table variables, and
    3. joins are being done between the local and temporary tables on string columns, and
    4. the local database's default collation is not guaranteed to be a particular collation

    Essentially, the dynamic nature of DATABASE_DEFAULT allows one to deploy code to any DB on any instance, not knowing what the default collation will be, yet still wanting to work with whatever that collation happens to be. The most common use case I can think of is 3rd party software that will work with whatever collation you prefer.

    The reason that DATABASE_DEFAULT is not a universal fix is that the column causing a collation conflict error might not be using the database's default collation. The database could be using Latin1_General_100_CI_AS but the column might be using Latin1_General_100_BIN2. In that case, assuming that the instance is not also using Latin1_General_100_BIN2, then it doesn't matter if the instance is using Latin1_General_100_CI_AS or anything else, if the error is coming from a temp table. The local column is using Latin1_General_100_BIN2 so in most cases you want the predicate to also use Latin1_General_100_BIN2, and using DATABASE_DEFAULT would equate to Latin1_General_100_CI_AS, which would not behave as expected. For scenarios like that, you would actually want to specify COLLATE <column's_collation> to maintain the column's intended behavior.

    Take care,
    Solomon...

    _____________________________________________________________
    SQL# https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap https://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR
    _____________________________________________________________



    Friday, May 8, 2020 7:26 AM
  • Hi there. I realize this is a rather old question, but for anyone looking for info on changing the collation of a SQL Server instance (especially after it's been in use for any amount of time), please see the following post of mine that provides a detailed description of the undocumented "sqlservr.exe -q" option:


    I absolutely recommend against using this option. Solomon suggest that if there is an error with a user database, the database is rolled back. My experience is that it stops right there and leaves your database half way. That was a while, and Solomon has probably tested more thoroughy than I have. However, to be able to roll back, it would have to one of the following:

    • Run all in a single transactions --> transaction log will explode.
    • Take a backup and restore in case or error. --> Lots of disk space and time for a large database.
    • Take a database snapshot and revert --> Likely to fail, as many pages have to be written to the snapshot when indexes are being rebuilt. And if revert is successful you have a 0.5 MB log file.

    And it is worth pointing out that depending on the collation change you make, errors can be more than likely. For instance, a change between CI and CS is very likely to have problems in either direction. The only collation that is really safe is between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS if you only have nvarchar columns. I have not verified it, but I believe that for nvarchar these collations are identical. (For varchar, they are quite different.)

    If you want to try this path for a single database, restore a backup on a server you set up for the exercise.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 8, 2020 8:59 AM