Using ''IN'' Function

Question

Using ''IN'' Function

ste.cox

Mr or Mrs. 500

Points: 584
More actions
August 21, 2007 at 7:39 am

#121248

Hi,
I have the following sql and when i run the 2 statements on their own they are fine but when i try and use the IN function i get and error.
------------------
select t2.s_studentreference "Student reference",t1.p_forenames "Forenames",t1.p_surname "Surname" from capd_person t1 left outer join capd_student t2 on t2.s_id=t1.p_id WHERE t2.s_studentreference IN (SELECT studentID FROM OPENQUERY(MSQLREVIEW,'SELECT studentID,student FROM Reports
WHERE (((classwork = "Poor" or homework = "Poor" or midterm = "Poor" ) AND session = "March2007") AND TG = "A10-")
GROUP BY studentID
HAVING count(*)>=3
ORDER BY TG ASC'))
----------------
Error = Server: Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
I have searched on the net but had no luck. anyone have any ideas???
thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can you provide the DDL for all the tables involved? In the script, be sure to include the collation of the character fields.

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 2

For the main statement the data is on a SQL Server 2005 for the other on its on a MYSQL Server.

sorry if thats not what you needed. iam still trying to get my head around it all. iam not the one maintaining the servers just trying to link them. plus i havent done anythign like this since i left uni over a year ago (and then nothing this complicated)

thanks again for the help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

The problem you are having is a collation conflict. Somewhere in your query, you are comparing two character fields that have different collations. Without knowing which tables are using which collations for their character fields, it is difficult to tell you how to fix it.

we need the Data Definition Language used to create the tables, but we need to know the collation of each of the character fields.

create table dbo.MyTable (

PKId int identity(1,1),

ACharacter varchar(10) collate Latin1_General_CI_AS

)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

The code pages for the data are different. Meaning the way in which the data is encoded is causing problems. Since the OR works, I'm guessing there's some bug here.

Report it on Microsoft Connect.http://connect.microsoft.com/sqlserver

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 5

How would i find out the collation of a table? i have tried puting the results of one query into a temp table setting to collation to both types it listed but i keep getting the same error when i compare the 2

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Please post, at least, the tables ddl (what do the tables look like?)

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 7

thanks for all your help. i have managed to get it working now.

all i had to do was switch round the variables in the where statement.

i.e - it was "where A = B" but if i switch it to "where B = A" it works.

should have tried that sooner (must have been having a VERY long stupid moment)

thanks again for everyones help.