SQL change field Collation in a select

Question

i'm trying to do the following select:

select * from urlpath where substring(urlpathpath, 3, len(urlpathpath))
not in (select accessuserpassword from accessuser where accessuserparentid = 257)

I get the error:

Cannot resolve the collation conflict between 
"SQL_Latin1_General_CP1_CI_AI" and 
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Does anyone know how i can cast as a collation, or something that permits me to match this condition?

Thanx

Answer 1

You can even do the collate on the query to create a new table with that query, for example:

SELECT 
    * 
INTO 
    #TempTable  
FROM 
    View_total
WHERE 
        YEAR(ValidFrom) <= 2007 
    AND YEAR(ValidTo)>= 2007 
    AND Id_Product = '001' 
    AND ProductLine COLLATE DATABASE_DEFAULT IN (SELECT Product FROM #TempAUX) 

COLLATE DATABASE_DEFAULT causes the COLLATE clause inherits the collation of the current database, eliminating the difference between the two