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'.
精彩评论