SQL Error with "Turkish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

Question

I have a SQL query like this:

SELECT E.HESAP, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2 AS 'TEKLIF',
    E.MUSTERI,CONVERT(VARCHAR(10),B.ISL_TAR,103) AS 'TARIH', SUM(
    ISNULL(CAST(B.ODENEN_ANAPARA AS FLOAT),0)+ISNULL(CAST(B.FAIZ AS FLOAT),0)+
    ISNULL(CAST(B.BSMV AS FLOAT),0)+ISNULL(CAST(B.GECIKME_FAIZ AS FLOAT),0)+
    ISNULL(CAST(B.GECIKME_BSMV AS FLOAT),0)) AS 'YATAN', 
    (CASE WHEN C.DOVIZ_KOD = 21 THEN 'EUR' WHEN C.DOVIZ_KOD = 2 THEN 'USD' WHEN C.DOVIZ_KOD = 1 THEN 'TL' END) AS 'KUR',
    E.AVUKAT, CONVERT(VARCHAR(10),A.ICRA_TAR,103) AS 'İCRA TARİHİ', CONVERT(VARCHAR(10),A.HACIZ_TAR,103) AS 'HACİZ TARİHİ'
    FROM TAKIP A, YAZ..MARDATA.BIR_TAHSIL B, 
    YAZ..MARDATA.S_TEKLIF C,P_TAKIP_SR D, AVUKAT E
    WHERE B.TEKLIF_NO1 = C.TEKLIF_NO1
    AND B.TEKLIF_NO2 = C.TEKLIF_NO2
    AND A.T_HESAP_NO = C.HESAP_NO
    AND C.HESAP_NO = B.HESAP_NO
    AND B.HESAP_NO = E.HESAP
    AND A.T_SRM = D.T_SR_ID
    AND A.T_STATU = 2
    AND A.T_SRM <> 6

But when I run it, I get an error:

Server Error in '/' Application.
Cannot resolve the collation conflict between "Turkish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Turkish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Source Error: Line 77: myConnection.Open(); Line 78: Line 79: SqlDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); Line 80: Line 81: // show the data

I can't find the error. Where is it?

Answer 1

Have a look at one of your AND clauses, where the data type is of type VARCHAR. You will need to specify the collation on both sides of the where to ensure you dont get this error.

EG:

WHERE B.TEKLIF_NO1 COLLATE SQL_Latin1_General_CP1_CI_AS = C.TEKLIF_NO1 COLLATE SQL_Latin1_General_CP1_CI_AS

Answer 2

The literal '/' will assume the default collation of the database.

Try

... B.TEKLIF_NO1 + '/' COLLATE Turkish_CI_AS + B.TEKLIF_NO2 AS 'TEKLIF' ...