A collation defines the code page sort order for unicode data types and code page characters. The error you get tells you that the definition between the two columns doesn't match so you need to explicitly specify what collation should be used, like this:
SELECT id
FROM TmpTable1
INNER JOIN TmpTable2
WHERE TmpTable1.name COLLATE DATABASE_DEFAULT = TmpTable2.name COLLATE DATABASE_DEFAULT
Good luck!