June 13, 2012 at 4:32 am
Hi Guys
First and foremost, before I decided to log this issue I did research through the forum and the internet (google) but didn't get a satisfying answer to the problem below.
1. I have a SQL Server with the following Properties
Name:..................BC
Product:...............MS SQL Server Enterprise Edition
Operating System:..Microsoft Windows NT 5.2 (3790)
"
"
"
Server Collation:....SQL_Latin1_General_CP1_CI_AS
2. I have several DB attached but the following two suppose to be identical with different names as they have the same tables etc
BWRH ..Collation..SQL_Latin1_General_CP1_CI_AS
CWRH ..Collation..SQL_Latin1_General_CP1_CI_AI
If I execute the following query in BWRH it returns the results well...
SELECT TOP 5 *
FROM acct_BWRH a
LEFT OUTER JOIN
irregular b ON a.acct_no = b.acct_no AND a.acct_type = b.acct_type
WHERE a.status <> 'closed' AND b.OD_limit < 0
But the same code executed on the CWRH returns the following error "Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."
I tried changing the CWRH in Options to SQL_Latin1_General_CP1_CI_AS but the error still persist.
How do I solve or where in the code should I implement the COLLATE SQL_Latin1_General_CP1_CI_AS in order to get the result set?
Regards,
AK
June 13, 2012 at 4:39 am
You will need to drop and rebuild all indexes, primary keys etc to enforce the new collation.
Or you could add COLLATE database_default to then end of any string comparisions
SELECT TOP 5 *
FROM acct_BWRH a
LEFT OUTER JOIN
irregular b ON a.acct_no = b.acct_no AND a.acct_type = b.acct_type COLLATE database_default
WHERE a.status <> 'closed' AND b.OD_limit < 0
June 13, 2012 at 4:56 am
anthony.green (6/13/2012)
You will need to drop and rebuild all indexes, primary keys etc to enforce the new collation.Or you could add COLLATE database_default to then end of any string comparisions
SELECT TOP 5 *
FROM acct_BWRH a
LEFT OUTER JOIN
irregular b ON a.acct_no = b.acct_no AND a.acct_type = b.acct_type COLLATE database_default
WHERE a.status <> 'closed' AND b.OD_limit < 0
Thanks it did work... the whole time I tried this
SELECT TOP 5 *
FROM acct_BWRH a
LEFT OUTER JOIN
irregular b ON a.acct_no = b.acct_no AND a.acct_type = b.acct_type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE a.status <> 'closed' AND b.OD_limit < 0
June 13, 2012 at 5:05 am
Just for the record, this issue can show up very quickly if a user database and tempdb have different collations, as all temp tables are created in tempdb and (as default) with tempdb's collation.
June 13, 2012 at 5:19 am
okbangas (6/13/2012)
Just for the record, this issue can show up very quickly if a user database and tempdb have different collations, as all temp tables are created in tempdb and (as default) with tempdb's collation.
I will backup the DB, and than create a new DB in a test environment with the default COLLATION and restore the backup on the DB.. Hope that will help and sort out the tempdb issue in the future, if the solution works in the test environment than i will implement on the live DB..
Thanks guys...
June 13, 2012 at 10:01 am
When you restore the backup all columns it will have the collation as they were in the databse at the time the backuop was created. The fact you have recreated the db witha different collation gets overwritten by the restore.
To change to collation of a column requires an "alter table" statement on each column that needs changing, which in turn needs indexes dropping before the alter and recreating after.
Mike
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply