Ya consegui el error muchas gracias, debía hacer el collate en el on y no en la clausula where asi:
Código SQL:
Ver originalINSERT INTO DDD_TOTAL_CLASE
SELECT t.periodo,
t.co_producto_pfz,
SUM(t.unidades) total_clase,
SUM(t.unidades * n.PESO) AS total_clase_kilos
FROM DDD_TMP AS T LEFT OUTER JOIN DDD_NOMBRES AS n ON N.NOMBRE_DDD COLLATE DATABASE_DEFAULT=T.co_producto COLLATE DATABASE_DEFAULT
GROUP BY T.periodo,
T.co_producto_pfz