SYMPTOM:
When a report that hits a SQL Server warehouse is run in Strategy 9.x-10.x, an error similar to the following is thrown:
CAUSE:
Note the portions highlighted in red. The first is "If this statement is a common table expression, an xmlanamespaces clause or a change tracking context clause..."
The second important part of the error is the DISTRIBUTION = HASH message. This is syntax that is specifically generated by Strategy SQL Engine when using the database connection type called SQL Server 2008 R2 Parallel Data Warehouse. This is set in the database instance being used.
Microsoft SQL Server Parallel is a completely different tool than Microsoft SQL Server 200x, however they are often confused. Parallel was actually bought by Microsoft and rebranded with a similar name even though it is completely separate.
ACTION:
Change the database connection type in the database instance to the correct one. For example, if the warehouse is actually on Microsoft SQL Server 2008, then choose that as the database connection type in the database instance. The project will have to be unloaded and reloaded or the Intelligence Server restarted for this to take effect.
After making this change to the correct database connection type, the SQL Engine will generate correct SQL for the warehouse and the error will not be thrown.
