SQL Server WITH not working, invalid syntax?

Question

Using the following query,

WITH 
    cteTest (Employee_ID)
    AS
    (
        SELECT employee_ID FROM pep.dbo.labor_ticket
    )
SELECT Employee_ID FROM cteTest;

I get the following return:

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

Looks right to me. I asked a similar question about a subquery but the same logic does not apply here as I have aliased the table with the name cteTest. What's missing?

Answer 1

Try putting a semicolon in front of the "WITH".

;WITH 
    cteTest (Employee_ID)
    AS
    (
        SELECT employee_ID FROM pep.dbo.labor_ticket
    )
SELECT Employee_ID FROM cteTest;

Answer 2

Looks like this particular database is on SQL Server 8.0 which does not support CTE. Looks like I'll have to settle with subqueries.

Figures, working for a company with 3 different versions of SQL Server that I would have overlooked this. The other servers are 9.0 and support this functionality just fine and I've never had to write something with a CTE against this particular DB, learn something new every day :)