asked on

Cannot resolve collation conflict for equal to operation.

Hi,

Can anyone tell me why I am getting the following error message and how to solve it for the SQL code below:

Cannot resolve collation conflict for equal to operation.

As you can see, ContactId is the primary Key I always link back to.

Points worth noting.

All tables are in the same database
ContactId has the same field type, field length and collation type in all instances.
I am running this on SQL server 2000 in query analyzer

SELECT (SELECT COUNT(*)
FROM tblcontactprofiles
WHERE contactid = tblcontacts.contactid AND activestatus = 'A') AS Profiles, ISNULL(Surname, '') + ', ' + ISNULL(Title, '') + ' ' + ISNULL(Christian, '')
AS Contact, Surname AS Expr1,
(SELECT MAX(contacttype)
FROM tblcontacttypes
WHERE contacttypeid = tblcontacts.contacttypeid) AS jobfunction,
(SELECT MAX([emailaddress])
FROM tblemailaddresses
WHERE contactid = tblcontacts.contactid) AS email,
(SELECT COUNT(*)
FROM tblinvestmentplans
WHERE contactid = tblcontacts.contactid) AS investmentplans, LEN(AdditionalResponsibilities) / 4 AS AdditionalContactTypes,
CustomerAccountNumber AS Expr2, *
FROM dbo.tblContacts
WHERE (EducationNumber = 3573)

thanks in advance

Ken Selvia

Can you narrow down which select is causing the problem? Comment out each subslect until you find the specific column causing the problem. If contactid has the same collation in all db's it looks like the problem would be with contactypeid.

Be sure the default collation for model is the same as your db. If not tempdb will have a different collation and it is possible intermediate worktables will have different collations.

kinton

ASKER

Sorry, that is another point I should have made - it appears to be no individual select clause, as you can see there are four sub queries, if I remove three of them, it does not matter which three it will work, but as soon as I have more than one sub query it fails!

Ken Selvia

Run:

SELECT DATABASEPROPERTYEX('tblContacts', 'Collation') AS 'Database Level Collation'
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') AS 'Database Level Collation'

EXEC sp_help 'dbo.tblContacts' -- and for other tables too and examine column collations

And make sure all collations are the same.

You can also specify the collation for a column:

WHERE contactid collate database_default =

but if they really are all the same, I don't know what difference that would make. You could do it anyway just to see what happens.

kinton

ASKER

no luck, all the collations are identical!

Ken Selvia

Sorry Kinton, I am out of ideas. Hopefully someone else will come up with another suggestion.

sandeshj

is this question still open??

kinton

ASKER

I hope so! It is still a problem!

Kselvia - Thanks for trying

ASKER CERTIFIED SOLUTION

sandeshj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knroc

Sandesh -

I was having the same problem and your solution helped me. I was updating a column in a sql server 2000 database with the value from a table in an oracle database. Thanks a lot!

- knroc

UPDATE CommonTables.dbo.AcciCodes
SET CommonTables.dbo.AcciCodes.actv_title = DV.FTVACTV_TITLE
FROM CommonTables.dbo.AcciCodes AS CTA INNER JOIN DWHS..DATAMGR.SS_FTVACTV AS DV ON DV.FTVACTV_ACTV_CODE collate database_default = CTA.actv_code
WHERE CTA.actv_code IS NOT NULL;

sandeshj

Geee :0)
Its a great feeling that my comments have helped others solve their problems.

Cheers knroc,
Sandesh

sandeshj

By the way,
Do i get any points for that :)

Cheers,
Sandesh

sarah56565

Thank you, this solution helped me too!