Multi-Shoring

Subscribe via Email

Subscribe to RSS feed

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.

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

Leave a Reply