Results 1 to 1 of 1

Thread: Changing SQL collation on a column

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    3,492

    Default Changing SQL collation on a column

    I've chewed through this week trying to figure out a way to systematically change collation when I need it. I have some applications that are installed at at many different customer sites and some are SQL while some others are still embedded. When I convert to SQL, I have to keep the Collation as "SQL_Latin1_General_CP1_CS_AS" so that the behaviors are the same for embedded and SQL versions when it comes to sorting on unique indexes.

    BUT... more and more, I have found huge advantages when creating searches for certain strings in descriptions, notes, names etcetera by creating an embedded SQL procedure that can do that search on fields which now should be changed to SQL_Latin1_General_CP1_CI_AS (so searching for "John" will also find "john" or "JOHN").

    SO... I have got a working process on my development computer that checks a system database "SQL_DataVer" and then if it is insufficient AND the customer is using MSSQLDRV, it will automatically backup the db and change the collation on specified fields.

    I also tested it with two computers having the data open and it seems to work fine so I am thinking/hoping that it will work just fine in a production scenario where more than one person has the data open.

    There are 2 Caveats:

    1. I have been unable to find a way to validate that the backup was successful (assuming that it was done at a workstation). My hope was that if I could check for that, then I would simply abort the attempt to change the collation if the backup was unsuccessful.

    2. I am not sure which security group the user should be a member of to be able to make these alterations. If I could check for this in advance, then I would also just simply not attempt the change if they did not have the correct security membership.... otherwise I think my user is going to get a bunch of SQL error messages which will freak them out and spawn a support call.

    Anyway, I have put this all into a package that you are welcome to try/test/modify.

    Hopefully it helps somebody.

    Mike

    PS... I did this in DF19.0... Thanks to everyone who chimed in and helped me sort through some early issues that I had.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •