How to change SQL server 2008 collate after installation

Tüm Yanıtlar

• 

  

  0

  Oylamak İçin Oturum Aç

  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.
  

  • Yanıt Olarak Öneren VidhyaSagar 12 Aralık 2008 Cuma 07:13

  30 Temmuz 2008 Çarşamba 00:03
• 

  

  3

  Oylamak İçin Oturum Aç

  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.

  • Yanıt Olarak Öneren Bojan Skrchevski 13 Aralık 2011 Salı 14:29
  • Yanıt Olarak İşaretleyen Olaf HelperMVP 7 Nisan 2013 Pazar 18:16

  30 Temmuz 2008 Çarşamba 19:13
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  • Yanıt Olarak Öneren Ahmad Bani Naser 12 Ocak 2012 Perşembe 12:37

  5 Ağustos 2008 Salı 12:56
• 

  

  0

  Oylamak İçin Oturum Aç

  According to the link that Joe posted, the switch should be /QB, not /QN.
  

  6 Ağustos 2008 Çarşamba 19:53
• 

  

  0

  Oylamak İçin Oturum Aç

  I also tried with /QB, but with this switch it's showing me same problem.

   

   

  6 Ağustos 2008 Çarşamba 20:06
• 

  

  0

  Oylamak İçin Oturum Aç

  I hope /QB parameter no more exits in SQL 2008 for silent installation, you need to use /Q parameter for the same.

   

  7 Ağustos 2008 Perşembe 10:56
• 

  

  0

  Oylamak İçin Oturum Aç

  I tried /Q option too, but it was also not working.

  9 Ağustos 2008 Cumartesi 12:21
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  28 Ağustos 2008 Perşembe 16:08
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  8 Eylül 2008 Pazartesi 21:20
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  19 Eylül 2008 Cuma 09:55
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  • Yanıt Olarak Öneren Francois Michael Dain 28 Nisan 2009 Salı 07:39

  29 Eylül 2008 Pazartesi 09:07
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  10 Aralık 2008 Çarşamba 16:27
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  ---

  29 Aralık 2009 Salı 17:13
• 

  

  0

  Oylamak İçin Oturum Aç

  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/

  Setting and Changing the Column Collation

  http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

  Easy way to change collation of all database objects in SQL Server

  
  

  ---

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

  
  

  
  
  
  

  • Düzenleyen Kalman Toth 14 Kasım 2017 Salı 18:45

  9 Ocak 2010 Cumartesi 19:11
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  1 Şubat 2010 Pazartesi 10:21
• 

  

  0

  Oylamak İçin Oturum Aç

  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!

  19 Şubat 2010 Cuma 15:23
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  25 Ağustos 2010 Çarşamba 14:04
• 

  

  2

  Oylamak İçin Oturum Aç

  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?)

  • Yanıt Olarak Öneren Konsulent Trøndelag 31 Mart 2011 Perşembe 12:39

  14 Eylül 2010 Salı 07:51
• 

  

  0

  Oylamak İçin Oturum Aç

  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,

  16 Eylül 2010 Perşembe 01:03
• 

  

  0

  Oylamak İçin Oturum Aç

  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
  
  
  
  

  • Düzenleyen Kalman Toth 14 Kasım 2017 Salı 18:45

  15 Ekim 2010 Cuma 13:08
• 

  

  0

  Oylamak İçin Oturum Aç

  will it work in cluster also ?? or we having some different way to implement in Cluster Env. 

  ---

  india

  22 Temmuz 2011 Cuma 07:10
• 

  

  0

  Oylamak İçin Oturum Aç

  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/

  • Yanıt Olarak Öneren MSSQL DBA 4 Ağustos 2011 Perşembe 08:39
  • Yanıt Olarak İşaretleyen Olaf HelperMVP 7 Nisan 2013 Pazar 18:15

  2 Ağustos 2011 Salı 22:15
• 

  

  0

  Oylamak İçin Oturum Aç

  It works but it destroyed all my DB ... :/

  22 Ocak 2013 Salı 23:19
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  22 Ocak 2013 Salı 23:26
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  23 Ocak 2013 Çarşamba 04:47
• 

  

  0

  Oylamak İçin Oturum Aç

  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 sites — Collations     •     Module Signing     •     SQLCLR
  _____________________________________________________________

  
  

  8 Mayıs 2020 Cuma 06:58
• 

  

  0

  Oylamak İçin Oturum Aç

  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 sites — Collations     •     Module Signing     •     SQLCLR
  _____________________________________________________________

  
  

  
  

  • Düzenleyen Solomon Rutzky 8 Mayıs 2020 Cuma 07:38

  8 Mayıs 2020 Cuma 07:26
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  8 Mayıs 2020 Cuma 08:59