Development

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.

Covid 19
COVID-19: Digital Insights For Enterprise Action

Access Perficient’s latest insights into how you can leverage digital technologies to not only respond to the pandemic, but drive your operations forward and deliver experiences your customers need.

Get Informed

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

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up