Results 1 to 2 of 2

Thread: Collation error

  1. #1
    Join Date
    Aug 2003

    Unhappy Unanswered: Collation error


    Our SQL servers have been setup to collate American. However, our pc's are setup for South Africa. This is creating a nightmare for us as we use Great Plains, which is an American product with American date format.

    We are having difficulties in doing any development due to this issue.

    Can someone please advise me as to whether there is another way around this ?


  2. #2
    Join Date
    Oct 2004
    Durban - South Africa
    I hear you with this problem as i have experienced the exact same thing.

    The only way that i know to cope with it is to use the collate keyword which 'casts' the tables to a common collation so the join makes sense.

    Collations specify the way values are compared (eg Case sensitive, sort order), so be careful when choosing the one to cast to.
    UPDATE tblPrepTemplate
    SET Reason = 'Do not contact'
    FROM tblPrepTemplate A
    inner join [Do Not Contact].DoNotContact.dbo.TblDoNotContacts B
    ON a.TeleW = b.ContactNumber
    collate Latin1_General_CS_AS
    WHERE Reason Is Null

    It is possible to change the collation of a table as well, for example

    CREATE TABLE MyTable (PrimaryKey int PRIMARY KEY, CharCol varchar(10) COLLATE French_CI_AS NOT NULL ) 
    ALTER TABLE MyTable ALTER COLUMN CharCol varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
    I don't think that Collations do not affect the date format
    If at first you don't succeed, call it version 1.0

Posting Permissions

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