开发者

SQL - Incorrect syntax error 'with'

Hi can you please help me with this, Im using SQL Server 2005, im getting the "Incorrect syntax near the keyword 'WITH'" error, Thanks

create table #Act(
iAId int, 
iPAID int, 
sResource nvarchar(10), 
sName nvarchar(50), 
W1 int, 
W2 int, 
W3 int)

    INSERT INTO #Act (iAId, iPAID, sResource, sName, W1, W2, W3)
        SELECT AC00000.iAId, AC00000.iPAID, AC00000.sResource, 
               Activities.sName, WK00000.W1, WK00000.W2, WK00000.W3
        FROM   AC00000 INNER JOIN
               Activities ON AC00000.iActTypeId = Activities.iActivityId INNER JOIN
               WK00000 ON AC00000.iAId = WK00000.iAId
    ;WITH acts (iAId, iP开发者_如何学JAVAAID, sResource, sName, W1, W2, W3) AS
    (
         SELECT parent.iAId, 
                parent.iPAID,
                parent.sResource, 
                parent.sName, 
                parent.W1, 
                parent.W2, 
                parent.W3
         FROM   #Act parent
         WHERE iPAID is null

         union all

         SELECT child.iAId, 
                child.iPAID,
                child.sResource, 
                child.sName, 
                child.W1, 
                child.W2, 
                child.W3
         FROM   #Act child

        join acts
        on child.iPAID = acts.iAID
    )
    select * from acts


WITH (Common table expressions) only works in SQL Server 2005 or above.

Even at compatibility level 80, WITH statements work in SQL Server 2005.

  • Check that you are not running SSMS 2005 against a SQL Server 2000 instance.
  • Check that your server has only one instance of SQL Server, and it is the 2005 instance.
  • run this to check

select @@version


Your exact error text comes up when you use "with" against SQL Server 2000

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜