SQL - Incorrect syntax error 'with'

Question

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, iPAID, 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

Answer 1

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'.