Post reply

Changin database collation

  • March 18, 2008 at 2:35 pm

    #122707

    Hi !

    Can you help me please ?

    I had to change our database (with data) collation from French_CI_AS to SQL_Latin1_General_CP850_CI_AI.

    Database\Properties\Options\Collation

    The database was on line...:hehe:

    Is it the way to change database collation ? I am not pretty sure...;)

    After doing this, i noticed that data on fields with “varchar(max)” type were truncated.

    For example, even if i put 100 characters by the INTERFACE, no error but only 12 characters are really saved in the database. But when, i put 100 characters directly in the database, it's fine. Troncation happens only on these types of fiels et always the same number of characters 12.

    Can anyone knows why and how avoid this ? Where is the problem, changing the collation or our application ?

    Thank you very much 🙂

    I work with SQL server 2005 and application in C

  • March 18, 2008 at 3:11 pm

    #791148

    That would be the correct way using the gui. you could also use t-sql command alter database 'the name of the database' collate 'collation name'

    The issue you are seeing may be related to the collation chosen, as opposed to the method to change the collation?

    I have a similiar issue but different. I need to change the collation of the server/system databases. I try using the msdn command

    start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=smith_2008 SQLCOLLATION=SQL_Latin1_GeneralCP1_CS_AS SKUUPGRADE=1

    It is not being sucessful. I did a trick/test what if I couldn't do a re-install could I detach the system databases from a database set to the collation desired, copy those and attach them to the server I want to change. If I do this I still need to recreate logins and jobs, is there any other issue that I might be creating? It opens up and the databases look okay.

    Thoughts?

    Thanks 🙂

    Mark

  • March 18, 2008 at 3:13 pm

    #791150

    Look real hard at your application. The fact that you can enter the data and it is fine directly to the database points back to the application.

    Good luck;

    Mark 🙂

  • March 19, 2008 at 12:16 pm

    #791637

    Thanks 🙂

  • March 19, 2008 at 3:43 pm

    #791782

    You are welcome. My issue ended up being a typo

    I should have had SQL_Latin1_General_CP1_CS_AS for the collation not GeneralCP1

    Now how to script out the maintenance plans. 🙂

    Mark

  • June 27, 2008 at 11:22 am

    #835529

    Finally, it was about code pages and ODBC driver.

    different code pages (client and sql server). I have to change the default collation of the server and recreate the database.

Viewing 6 posts - 1 through 5 (of 5 total)

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