Incorrect syntax nears the keyword ‘with’” in SQL override

Problem:

Following error occurs when a Common Table Expression (WITH) has been used as SQL override, when connecting to SQL database:

RR_4035 : SQL Error [Microsoft OLE DB Provider for SQL Server: Statement(s) could not be prepared.
SQL State: 42000 Native Error: 8180
State: 1 Severity: 16
SQL Server Message: Statement(s) could not be prepared.

Microsoft OLE DB Provider for SQL Server: Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
SQL State: 42000 Native Error: 319
State: 1 Severity: 15
SQL Server Message: Incorrect syntax nears the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Microsoft OLE DB Provider for SQL Server: Incorrect syntax near the keyword ‘WITH’.
SQL State: 42000 Native Error: 156
State: 1 Severity: 15
SQL Server Message: Incorrect syntax near the keyword ‘WITH’.

Solution:

To resolve the issue, place a semi-colon before the SQL override. As the Common Table Expression begins with the keyword with, semi-colon needs to be placed before the clause with.

Example:

; WITH abc (EMPID, EMPNAME, DEPT) AS

(
SELECT EMPID, EMPNAME, DEPT 
FROM SQL_EMP
WHERE DEPT is NULL
UNION ALL
SELECT e.EMPID,e.EMPNAME, e.DEPT 
FROM SQL_EMP e INNER JOIN abc m  
ON e.DEPT = m.EMPID
) 
SELECT EMPID, EMPNAME, DEPT FROM abc