Post reply

How to change SQL Server Collation

  • July 11, 2009 at 4:55 am

    #1023475

    SoehnelS (7/10/2009)

    To fix the Problem I used the command (default instance):

    start /wait setup.exe /qb REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD=useyourownpassword SQLCOLLATION=Latin1_General_CI_AS

    Now the server use the wished collation Latin1_General_CI_AS.

    No, does'nt, uses Latin1_General_CI_AI...

    I don't know why...

    du.pereira (7/1/2009)

    1. Stop the running server.

    2. Open a Command Prompt and go to the SQL Server directory.

    3. Type:

    sqlservr -m -T4022 -T3659 -q"new collation"

    example:

    sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"

    The server will start in single user mode and change all databases with all tables inside, to the new collation.

    Detached all User-Databases (already with the right collation),

    runing the (modified) command, getting some Messages

    (recreating indexes and so on till "recovery finished" or so),

    stoped and restarted the Server:

    All fine!

    THANKS!

    Greetings, Sven

  • July 14, 2009 at 9:21 am

    #1024486

    Hi du.pereira

    The solution sqlservr -m -T4022 -T3659 -q"new collation"

    works for me.

    Can you tell me the source of such solution. I have not come across this anytiem before.

  • July 15, 2009 at 6:50 am

    #1024983

    Sunil, I found this on a Brazilian DBA forum. It was very hard to find (almost hidden), and I tried to find the link for it, but I couldn't. I saved the command in a text file a long time ago.

    This command saved my a** me when we migrated from SQL 97 to 2005, when collation was set to compatibillity_(number).

    Best regards.

    Eduardo

  • August 14, 2009 at 8:04 am

    #1039367

    du.pereira (7/1/2009)

    I had the same issue for a long time with no avail. I didn't find the reinstall command useful and the alter database didn't help since it didn't change the tables collation. Every field that needs to be changed to a new collation and is attached to an index has to have the index dropped, the collation changed and the index recreated. That is a lot of work, and after searching for a long time, a friend of mine came with a very simple solution (undocumented), and I want to share with you.

    1. Stop the running server.

    2. Open a Command Prompt and go to the SQL Server directory.

    3. Type:

    sqlservr -m -T4022 -T3659 -q"new collation"

    example:

    sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"

    The server will start in single user mode and change all databases with all tables inside, to the new collation.

    PS: Sorry for my bad english (I'm from Brazil).

    Hello does anyone know what flag 3659 does? I know that -m is single user; -T4022 bypass automatically started processes. But unsure what 3659 is used for here, as I thought it was the -q parameter which tag the new collation.

    Any ideas?

  • August 14, 2009 at 10:23 am

    #1039518

    Hello does anyone know what flag 3659 does? I know that -m is single user; -T4022 bypass automatically started processes. But unsure what 3659 is used for here, as I thought it was the -q parameter which tag the new collation.

    Any ideas?

    I googled and found this quick TRACE FLAG reference, but couldn't find the exact functionality for the xx59:

    Yes, the trace flags are grouped by area of SQL Server - the first two digits always denote the area. For example:

    6xx are Storage Engine access methods

    8xx are buffer pool

    12xx are lock manager

    14xx are database mirroring

    25xx, 52xx are DBCC related

    30xx, 31xx, 32xx are backup/restore

    36xx are SQL Server general 'run-time'

    55xx are FILESTREAM

    73xx, 74xx are query execution

    76xx are full text related (?)

    75xx are cursors

    82xx are replication

  • December 5, 2009 at 7:41 pm

    #1088226

    This script saved my head: tks a lot

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

  • December 17, 2009 at 7:06 am

    #1093467

    I have an SQL Server 2005 instance where the default collation is set to Latin1_General_CI_AS, but the user databases are set to SQL_Latin1_General_CPI_CI_AS.

    I need to change the default/server collation to SQL_Latin1_General_CPI_CI_AS as well (i.e. same as the user databases).

    Looking at the comments above, are you/we saying that I need to take my databases that are set to SQL_Latin1_General_CPI_CI_AS offline (having made backups of everything), and then do the following:-

    1. Stop the running server.

    2. Open a Command Prompt and go to the SQL Server directory.

    3. Type:

    sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS"

    And then I'll be left with an instance where eveything is set to

    SQL_Latin1_General_CP1_CI_AS?

    Is anything else required?

    Are there any downsides to doing this?

  • December 17, 2009 at 8:42 am

    #1093539

    Hi Paul.

    You must detach your user databases, I don't think taking them offline will work, you have to test it to make sure.

    There is nothing else required, not that I know...

    Just make sure your backups are updated while doing that.

    Regards.

  • December 17, 2009 at 8:48 am

    #1093547

    Thanks

    I have a user database that has been created using the default - do I leave that attached (i.e so that it is changed)?

  • December 17, 2009 at 9:00 am

    #1093557

    paul_shears (12/17/2009)

    Thanks

    I have a user database that has been created using the default - do I leave that attached (i.e so that it is changed)?

    Leave only the databases that you want to change online so the server won't do all the processing for nothing.

  • December 17, 2009 at 9:07 am

    #1093560

    Thanks.

    My alternative method, was to back up/export the user databases, delete the instance, create a new instance with the correct collation and then re-create/import the databases.

    Does your method achieve the same thing?

    Oh...and does having databases with a different collation to the server cause problems?

  • December 17, 2009 at 9:35 am

    #1093575

    paul_shears (12/17/2009)

    Thanks.

    My alternative method, was to back up/export the user databases, delete the instance, create a new instance with the correct collation and then re-create/import the databases.

    Does your method achieve the same thing?

    Oh...and does having databases with a different collation to the server cause problems?

    You can reinstall your instance or you can also do this procedure below (quoted from the first page in this topic):

    dharper3 (3/29/2006)

    Straight from BOL, the source of all knowledge:

    To rebuild system databases and specify a new system collation

    Insert the SQL Server 2005 installation media into the disk drive.

    Run the following command from the command prompt:

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

    For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.

    Important The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the section below on REBUILDDATABASE for more information.

    The /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files.

    The /qb switch allows display of basic Setup dialog boxes. Error messages are also displayed.

    With this command that I described before you won't have to reapply any service pack as you'd have by running the above. With the command that I posted the server you do the "Drop index, Change collation, Re-create Index" for all tables in all databases currently attached, that you'd do "by hand" by using "ALTER TABLE <table> ALTER COLUMN <column definition> COLLATE <new_collation>.

    Remember that all databases attached will be changed to the new specified collation. If you want some specific database to remain with a different collation, detach it!

  • June 11, 2010 at 11:39 pm

    #1179513

    Hi All,

    I had used this solution..and it had worked for me in past.

    now I have the same situation but this time its not working and give error:

    Error: 17112, Severity: 16, State: 1.

    An invalid startup option q was supplied, either from the registry or the command prompt. Correct or remove the option.

    thanks,

    Atul Gaikwad

    Thanks & Regards,

    Atul Gaikwad.
    email:atulx86@rediffmail.com

  • June 12, 2010 at 5:22 pm

    #1179569

    Hello Atul,

    This is strange. Are you using SQL 2005? I used this command last week on one of our development databases which was incorrectly installed and worked fine. The server is up-to-date with service packs and updates.

    Regards.

  • November 7, 2010 at 1:40 pm

    #1247473

    ALTER DATABASE AdventureWorks COLLATE SQL_Latin1_General_Cp1250_CI_AS

    The above command is giging error message.....can someone tell what is wrong? Thanks

Viewing 15 posts - 31 through 45 (of 76 total)

You must be logged in to reply to this topic. Login to reply