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.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

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