I have come across an error several times when working with R, that the RODBC package can't execute an SQL query string, but when I type the exact same string directly to a SQL Server query it works. Note that my strings contained umlauts.
I'm answering this question myself to help others avoid long internet searches if instead it can be simply reduced to this.
Almost always it was just an UNICODE error. Using umlauts or other non-unicode symbols in a R-string with the RODBC package produces this kind of error. So before trying to break it up into sub-queries as suggested by the error statement, check if your string contains only unicode characters.
If not, then the query is really to complex and needs to be split into sub-queries. For this, please refer to the other questions about this topic.