Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 6 of 6

Thread: Scripting of Collation Change

  1. 06-07-2010, 08:11 AM #1
    Garf is offline Senior Member
    Join Date
    May 2006
    Posts
    190

    Default Scripting of Collation Change

    Has anyone sucessfully written a script to move a DB and all it's tables and columns from one collation to another.

    I am trying to update to ML9 from 8 and I can't because of a collation conflict.

    I have changed the DB's collation to SQL_Latin1_General_CP1_CI_AS but then you have to go through and alter ALL the columns to that collation. I found a bit of code to do this on ALL the columns in one bit go (well above my SQL knowledge) and I get the ALTERS Columns, but I can get them to alter where there is dependent on the column.

    I can go off and delete the ones that don't work, then go into the design and alter the collation manually, but that is very time consuming and I am worried that this will take our website off line for hours when I finally move across to ML9.

    Anyone got a bit of script to ignore any of these sort of errors, as if it works in design it MUST be possible in script surely.
  2. 06-07-2010, 10:29 AM #2
    AspDotNetStorefront Staff - Scott's Avatar
    AspDotNetStorefront Staff - Scott is offline Administrator
    Join Date
    Mar 2007
    Location
    Ashland, OR
    Posts
    2,390

    Default

    Depending on the collation your DB is on, there probably aren't any collation issues and you can work around this pretty easily.

    Just open up the upgrade scripts and search for 'COLLATION' and remove those references. The script should run fine. Make sure you have a backup first just in case obviously.
    ECommerce Shopping Cart
  3. 06-07-2010, 02:00 PM #3
    webopius is offline Senior Member
    Join Date
    Nov 2008
    Location
    London, UK
    Posts
    440

    Default

    Hi

    When we did an upgrade of a v7.x system to v9.0.1.3, the one collation issue we found was in the 8.0.1.2 to 9.0 upgrade script. All that was needed to fix it was to open the upgrade script and look for a reference to 'SQLLatin1_General_CP1_CI_AS' which was in the creation of a column in the EntityStore table.

    As Scott says, Removing this collation reference allowed the upgrade to continue to version 9.0.1.3 with no further issues.
    Webopius.com: ASPDotNetStorefront web development and ecommerce project management
    Webopius is now a Sage Pay Approved Partner
  4. 06-08-2010, 06:57 AM #4
    Garf is offline Senior Member
    Join Date
    May 2006
    Posts
    190

    Default

    Excellent, I was wondering if I could get away with just taking that out, but I thought I'd try moving it all to the default collation
  5. 08-26-2011, 06:15 AM #5
    mskVenus is offline Junior Member
    Join Date
    Aug 2011
    Location
    China
    Posts
    25

    Default

    Quote Originally Posted by AspDotNetStorefront Staff - Scott View Post
    Depending on the collation your DB is on, there probably aren't any collation issues and you can work around this pretty easily.

    Just open up the upgrade scripts and search for 'COLLATION' and remove those references. The script should run fine. Make sure you have a backup first just in case obviously.
    I have search for 'COLLATION' in the upgrade script:8.0.1.2 to 9.0.sql, but I get nothing in here? so could you tell me where is the 'collation'?
  6. 08-26-2011, 08:43 AM #6
    AspDotNetStorefront Staff - Scott's Avatar
    AspDotNetStorefront Staff - Scott is offline Administrator
    Join Date
    Mar 2007
    Location
    Ashland, OR
    Posts
    2,390

    Default

    We don't put any collation references in scripts anymore, as they're generally not necessary and as this thread has shown, can actually cause issues.

    Are you having some kind of problem with the upgrade script?
    ECommerce Shopping Cart