Collation error

hi,

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 ?

ThanksI 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 )
GO

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