dba_sql


I am having this error when using execute query for CTE

Help will be appriciated




Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

Jens Suessmeyer


Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

MVP User

As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH":
 
;WITH myCTE AS
...
 

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
 
 

I am having this error when using execute query for CTE

Help will be appriciated






Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

JGilbertie

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@ClassificationTreeId int)

RETURNS @ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts This is the exact syntax found in the help files, no





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

MVP User

I was able to run that batch on my end with no errors once I uncommented the insert line...
 

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
 
 

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@ClassificationTreeId int)

RETURNS @ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts This is the exact syntax found in the help files, no





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

JGilbertie

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement shot in the dark, but I can't see any difference from examples I've found for using WITH.





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

MVP User

None that I know of.  The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error.
 
Regardless, you should probably make sure yours is set correctly.  Right-click on your database in SSMS, click Properties, then Options.  Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on.  Can you use CTEs at all (outside of UDFs )  And did you try adding a semicolon before the WITH, as I suggested before   It appears to be unnecessary on my end, but it's always a good idea anyway...
 

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
 
 

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement shot in the dark, but I can't see any difference from examples I've found for using WITH.





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

JGilbertie

So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.

A silly mistake, but I wouldn't have realized it, Thanks for your help!





Re: CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause,

echovault

I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,