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

Answers

  • 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,
    Thursday, July 19, 2007 10:30 PM

All replies

  • 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
    ---
    Thursday, August 03, 2006 2:54 PM
    Moderator
  • 
    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

    • Proposed as answer by MatteoDS Monday, February 16, 2009 10:31 AM
    Friday, August 04, 2006 7:30 PM
  • 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?

    Tuesday, August 08, 2006 11:53 PM
  • 
    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?

    Wednesday, August 09, 2006 3:00 AM
  • 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.

    Wednesday, August 09, 2006 3:12 PM
  • 
    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.

    Wednesday, August 09, 2006 5:00 PM
  • 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!

    Friday, August 11, 2006 4:25 PM
  • 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,
    Thursday, July 19, 2007 10:30 PM