Incorrect syntax near the keyword 'WITH'

Armonds

Just want to share info.

I'm was creating SQL Query to launch from Navision with ADO. The Query was some thing like:

WITH CONTR AS 
( SELECT CT.[No_],CT.[Customer Name],CT.[Address],
ROW_NUMBER() OVER (ORDER BY [No_]) AS RowNumber 
FROM dbo.Contracts CT )
SELECT * FROM CONTR WHERE RowNumber BETWEEN 1 AND 10

In SQL Management Studio this query executed without problems, but when launching from Navision I got error Message:
"The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message:
Incorrect syntax near the keyword 'WITH'."

The solution was simple, but I doubt I would find it without help of Google All what was needed was adding semicolon before WITH. This code works fine:

;WITH CONTR AS 
( SELECT CT.[No_],CT.[Customer Name],CT.[Address],
ROW_NUMBER() OVER (ORDER BY [No_]) AS RowNumber 
FROM dbo.Contracts CT )
SELECT * FROM CONTR WHERE RowNumber BETWEEN 1 AND 10

May be someone can explain the solution...

kriki

The reason is explained on how you connect to SQL through NAV.
If you use "Provider=SQLNCLI;" in the connection string it will work. This uses the native SQL driver, so you need to install it (don't remember though what exactly you need to install). If you install SSMS, it will work.
If you use "Provider=SQLOLEDB;" in the connection string it will NOT work. But to use this, you DON'T need to install anything.

Slawek_Guzek

From SQL Server 205 Books Online

WITH common_table_expression (Transact-SQL)
....
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

Try to track with SQL Server Profiler what exactly is executed. Perhaps there are some hidden statements executed like SET NOCOUNT ON before your query

Just a wild guess...

Slawek