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 ?
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.
SET Reason = 'Do not contact'
FROM tblPrepTemplate A
inner join [Do Not Contact].DoNotContact.dbo.TblDoNotContacts B
ON a.TeleW = b.ContactNumber
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
TrevorW If at first you don't succeed, call it version 1.0