KNOWLEDGE BASE

Error Code: 2F0F5E42 Occurred with SQL Server/Azure SQL Database using ORDER BY in Custom SQL


Published: 07 Sep 2022
Last Modified Date: 08 Sep 2022

Issue

While communicating with the SQL Server or Azure SQL Database, and using the ORDER BY clause in a custom SQL statement as shown below,  an error will display showing the following:

Error Code: 2F0F5E42
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


Example SQL:

SELECT 
 [Address].[AddressID]
FROM
 [SalesLT].[Address]
ORDER BY
 [Address].[AddressID]

Environment

  • Tableau Desktop
  • Tableau Server 

Resolution

Option 1

Use "TOP" in the custom SQL for the ORDER BY clause, following the limitations of Microsoft SQL (Transact-SQL).

Example SQL:
=====
SELECT TOP 2147483647
 [Address].[AddressID]
FROM
 [SalesLT].[Address]
ORDER BY
 [Address].[AddressID]
=====
Note: 2147483647 is not a fixed value, you can replace it to any value you want.

Option 2

Remove ORDER BY clause from the custom SQL, and try to sort data in the Tableau application if needed.
 

Cause

This is a limitation of Microsoft SQL.

Did this article resolve the issue?