Loading

Error "The ORDER BY clause is invalid in views Error Code: 2F0F5E42" Occurs When Using ORDER BY in Custom SQL with MS SQL Server or Azure SQL Data Source

Date de publication: Jan 24, 2025
Description

Issue

When using an ORDER BY clause in Custom SQL while communicating with MS SQL Server or Azure SQL database, the following error occurs:

 

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]

 

Cause

This is a limitation of Microsoft SQL.

 

Résolution

Option 1

Use "TOP" in the Custom SQL statement 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 statement and try to sort data in the Tableau application if needed.

 
 

Ressources supplémentaires

SELECT - ORDER BY Clause (Transact-SQL)

Numéro d’article de la base de connaissances

001496791

 
Chargement
Salesforce Help | Article