How to change SQL Server Collation

  • Hi,

    As Kumar said you need to rebuild system databases to change collation (in SQL 2000 you need just to Rebuild Master in 2005 the way to rebuild master has changed).

    Check the link

    http://msdn2.microsoft.com/en-us/library/ms144259.aspx

    Regards,

    Ahmed

  • Why should software (like TFS) depend on the collation of the server instance? A collation has no meaning unless used within the context of a database and starting with SS2K it has been possible to create databases whose collation is different from the instance collation. Unless of course TFS accesses the databases that get created on a new SS install.

    In any case, when issuing the

    alter database

    on an existing database be prepared to drop and recreate constrainst that use varchar columns.

    Has anyone had to drop and recreate anything else for a collate change to work?

  • Are you trying to change a default or named instance of sql server ? if it is not a default instance then you need to change the string MSSQLSERVER to the name of you instance

    CodeMinkey

  • Hi

    Yes I just had to drop table constraints and views. This is what I done.

    Database Properties - selected single user mode

    Script constraints and views to files

    delete constraints and views

    change collation to correct one

    Database properties - selected MultiUser mode

    recreate constraints and views.

    Happy again

  • Oh Microsoft are so infuriating sometimes.

    Why do you have to run this with /q?

    Why can't they let you know where the install log files are saved?

    Why can't they keep the list of switches somewhere handy so I don't have to go scouring the internet for it every damn time.

    Geez!

  • i usuually uninstall and reinstall with correct collation. I believe you can't use your present databases in the new collation you are trying to re-create the instance with.

  • I had the same issue. i have ran the script in a command prompt, but now it has been sitting for about 5 minutes. How long does it normally take to rebuild the database?

    Thanks for any help.

  • HI

    chmod

    I have got the same problem in sql server

    I tried Servaral Ways That i found in Goolgle

    And I over come the problem in this way

    That I created a NEw database With Defalut Collation then

    I Altered the Database Collation (With out Entering any data or Creating Tables)

    after changing collation i scripted all the objects to the new database

    and just reinsterd the data

    Now i am out the problem

    Just try this

    BUT This is a HARDWAY

  • chmod260 (3/28/2006)


    Hello, this message was previously posted in the General Discussion and got no answer. Perhaps this is the correct place...

    I was trying to install Visual Studio Team Foundation Server but the final check failed because my SQL Server 2005 installation had the wrong Collation setting:

    -------------------------------------

    The System Health Check has detected a problem that will cause Setup to fail.

    Description

    SQL Server collation is not set to one supported by Team Foundation Server.

    Workaround / Remedy

    The SQL Server collation is not set to one supported by Team Foundation Server. Change your collation to one that is supported and run setup again.

    -------------------------------------

    I googled around for a while and found that TFS requires a collation that is case insensitive and accent sensitive. My collation was accent INsensitive.(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275420&SiteID=1)

    To change Server Collation in SQL Server 2000 I usually use the rebuildm file but this is not supported anymore in SQL 2005.

    Microsoft recomends setup.exe to rebuild the master database (http://msdn2.microsoft.com/en-us/library/ms143269(SQL.90).aspx)

    and Tom Wisnowski details the operation (http://geekswithblogs.net/mskoolaid/archive/2005/12/17/63413.aspx)

    I tried this solution but... the master database was rebuilt but no change to change the collation (at least within a visible GUI).

    Question: How do you change the SQL Server 2005 Collation? Do you MUST rebuild the master database?

    -----------------------------------------------------------------

    You can not change sysstem database collastion. for that u have rebuild master database with new collation .

    u can change collation of user database from database property--> Option--Collation

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • My thanks to everyone for posting in this thread. Its great to see I'm not alone.

    Being the novice that I am, I'm praying that someone may have a simple solution to my collation problem. So here goes:

    In management Studio

    - I first connect to my Production SQL 2005 SP2 instance

    - Next I 'right click' on the instance to display properties\general\server collation and there I see 'Latin1_General_Bin' collation

    - So then I close 'properties', drill into 'Databases, and then expand my production database.

    - Then I right click on my production database and choose 'properties', and BANG! I get an error message that reads:

    ---------------------------------

    |->Cannot Show Requested Dialog.

    |-> An exception occurred while executing a transact sql statement or batch. (Microsoft.SQLServer.ConnectionInfo)

    |-> Cannot resolve the collation conflict between 'Latin1_General_Bin' and 'SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error 468)

    ---------------------------------

    The next thing that I did was to check the SQL Instance Collation for our test sql environment which is located on a different physical server and sure enough, the Instance's properties\general\server collation was the same 'Latin1_General_Bin' as the collation of my production SQL instance.

    - I then expanded the databases on the test instance, picked a random database and chose 'properties'.

    - And again, the collation for that database was 'Latin1_General_Bin'.

    ----------------------------------

    So at this point I'm led to believe that since my production instance and my test instance both have the same collation it must be my production database that has the collation mismatch.

    So guys do you know of any way that I can make my production database collation match my production instance collation? Because as it now stands, I can’t view the properties of my production database.

    Thanks so much to all of you for viewing this post.

    CC

  • I to am having issues with rebuilding the master database. I keep gettign the error:

    Please go to the Control Panel to install and configure system components.

    Help!!!!!!!!!!!!!!!!! VEry frustrating!!!!!!!!!!!!!! I am trying not to reinstall the whole server.

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • Its case sensitive. Use REINSTALL=SQL_Engine instead 🙂

  • "SQL_ENGINE" is case sensitive and needs to be "SQL_Engine".

  • 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).

  • Hi @ all

    and also sorry for my badly english (I'm from Germany:-)).

    My Problem:

    Existing Databases with collation Latin1_General_CI_AS should

    be attached into an SQL Server 2005 on an US-English System.

    When I install with collation Latin1_General the Server uses the

    collation SQL_Latin1_General_CP1_CI_AI (or _AS, can't remember,

    it's 12 minutes ago... :cool:).

    The collation change was required as an application shows a

    message "...incompatible collations between server and database.

    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.

    My question:

    Which collation I've to use whithin the setup to bypass the command?

    Thanks in advance!

    BTW:

    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.

    Looks very interesting, I'try this next week, Thanks!

    Greetings

    Sven

Viewing 15 posts - 16 through 30 (of 76 total)

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