Post reply

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

  • August 18, 2009 at 2:04 am

    #135572

    Can anyone help with the below problem ?

    I have tried adding the COLLATE DATABASE DEFAULT to various parts of the query but then get the

    Expression type int is invalid for COLLATE clause. error, the sql is below, can anyone help ?

    SELECT BRANDNAME,MODELNAME,VIEWCARNAME,SOURCENAME,VIDEOCARID,MODELFILE,PUBPATHACTIVE,PUBPATHINTERNETSMALL,PUBPATHINTRANETSMALL

    FROM AUTOMOTION,SOURCE,BRAND,MODEL,VIEWCAR LEFT JOIN VIEWPIC

    ON VIEWPIC.CARID = VIEWCAR.VIEWCARID

    AND (VIEWPIC.MODELVIEW2=24 OR VIEWPIC.MODELVIEW2=541 OR VIEWPIC.MODELVIEW2=591 OR VIEWPIC.MODELVIEW2=94 OR VIEWPIC.MODELVIEW2=1152)

    AND VIEWORDER=1 LEFT JOIN PUBLICATION

    ON PUBID=MODELPUBLICATION

    WHERE VIEWCAR.VIEWCARID = AUTOMOTION.VIDEOCARID

    AND VIEWCAR.VIEWCARSOURCE = SOURCE.SOURCEID

    AND VIEWCAR.VIEWCARMODELID = MODEL.MODELID

    AND BRAND.BRANDID = MODEL.BRANDID

    AND BRAND.BRANDNAME++MODEL.MODELNAME++VIEWCAR.VIEWCARNAME++AUTOMOTION.VIDEOCARNAMETEXT++CAST(SOURCE.SOURCEYEAR AS CHAR(4)) LIKE '%2007%'

  • August 18, 2009 at 2:55 am

    #1040678

    its ok I solved with the below for anyone that was interested.. 🙂

    SELECT BRANDNAME,MODELNAME,VIEWCARNAME,SOURCENAME,VIDEOCARID,MODELFILE,PUBPATHACTIVE,PUBPATHINTERNETSMALL,PUBPATHINTRANETSMALL

    FROM AUTOMOTION,SOURCE,BRAND,MODEL,VIEWCAR LEFT JOIN VIEWPIC

    ON VIEWPIC.CARID = VIEWCAR.VIEWCARID

    AND (VIEWPIC.MODELVIEW2 = 24 OR VIEWPIC.MODELVIEW2 = 541 OR VIEWPIC.MODELVIEW2 = 591 OR VIEWPIC.MODELVIEW2 = 94 OR VIEWPIC.MODELVIEW2 = 1152)

    AND VIEWORDER = 1 LEFT JOIN PUBLICATION

    ON PUBID = MODELPUBLICATION

    WHERE VIEWCAR.VIEWCARID COLLATE DATABASE_DEFAULT = AUTOMOTION.VIDEOCARID COLLATE DATABASE_DEFAULT

    AND VIEWCAR.VIEWCARSOURCE = SOURCE.SOURCEID

    AND VIEWCAR.VIEWCARMODELID = MODEL.MODELID

    AND BRAND.BRANDID COLLATE DATABASE_DEFAULT = MODEL.BRANDID COLLATE DATABASE_DEFAULT

    AND BRAND.BRANDNAME COLLATE DATABASE_DEFAULT ++MODEL.MODELNAME++VIEWCAR.VIEWCARNAME++AUTOMOTION.VIDEOCARNAMETEXT COLLATE DATABASE_DEFAULT++CAST(SOURCE.SOURCEYEAR AS CHAR(4)) LIKE '%2007%'

  • August 18, 2009 at 7:16 am

    #1040818

    i'd very much recommend that for future queries, you adopt the SQL 2k5+ syntax for doing joins, and use INNER JOIN instead of SELECT FROM TABLE1, TABLE2, TABLE3 ...

    makes it much easier to understand the control flow of the procedure, and i'm pretty sure they are deprecating the SELECT FROM TABLE1, TABLE2, TABLE3 syntax eventually.

Viewing 3 posts - 1 through 2 (of 2 total)

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