CODEWITHSUNDEEP
sql-serversyntaxsql-server-2000with-statement
Mohgeroth
Mohgeroth

Reputation: 1627

SQL Server WITH not working, invalid syntax?

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?

Upvotes: 1

Views: 3105

Answers (2)

Mohgeroth
Mohgeroth

Reputation: 1627

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 :)

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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;

Upvotes: 3

Related Questions