CTE syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CTE syntax error

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-03-31 : 08:35:37

hi

Whenever I am running any CTE query, it says

Incorrect syntax near the keyword 'with'

I am running as simple as the following

with MyCTE(x)
as
(
select x = convert(varchar(8000),'hello')
union all
select x + 'a' from MyCTE where len(x) < 100
)
select x from MyCTE
order by x

Any help will be appreciated.
ty

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 08:47:54

put a semicolon ';' in front of the with.

Jim

Everyday I learn something that somebody else already knew

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-03-31 : 08:51:03

thanks for the reply.

I have already tried that

it says "Incorrect syntax near ';'"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 09:20:55

I guess we'd need to see all of your code then since your query works for me.

Jim

Everyday I learn something that somebody else already knew

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-03-31 : 09:28:18

thanks for the reply.

before writing my query, i was just going through cte sources in google.
this website has some examples, i copied it and executed in my local machine.

and received the error.

you won't believe me, but the following is not executing.

with MyCTE(x)
as
(select x='hello')
select x from MyCTE

with or without ";"

i am using sql server 2005(the HELP-> ABOUT... shows me that)

ty

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-31 : 10:07:50

Your database MUST be in compatibility level 90 or higher!

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-31 : 10:08:38

quote:
Originally posted by cyberpd

i am using sql server 2005(the HELP-> ABOUT... shows me that)

No.
This is the version of your SSMS. Execute SELECT @@VERSION to get your current database version.

N 56°04'39.26"
E 12°55'05.63"

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-04-01 : 02:12:38

Thanks for the reply Peso.

I ran SELECT @@VERSION and the following is returned.
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

I have Sql Server 2000 installed in my machine, as well as 2005.
Please advice me what to do so that i can execute query in 2005 mode, i.e, i can get access to the functionalities that sql srvr 2005 provides.

ty

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-01 : 03:15:35

The SELECT @@VERSION tells me you are running your queries against a Microsoft SQL Server 2000 RTM (No service pack at all!).
As I wrote before, you must have compatibility level 90 or higher (SQL Server 2005) to use CTE's.

Upgrade your database server!
To do this from SQL Server 2000, you first MUST install SQL Server 2000 sp4 BEFORE you even begin upgrading to SQL Server 2005/2008/2008R2.

N 56°04'39.26"
E 12°55'05.63"

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-04-01 : 03:44:31

Peso

I don't know who use this machine other than me.
That's why i don't want to do anything (upgrade etc) to the sql server 2000.
I only want a Sql Server 2005 Version to be installed, clean, in this machine, which will reside with the Version 2000 side by side without disturbing it.
I am ready to install any Service Pack needed for 2005.
Please tell me if this is possible.
If yes, then do I have i to uninstall this management studio and reinstall everything from 2005 setup?
ty

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-01 : 04:20:58

You can ONLY use cte queries when connected to a SQL Server 2005 database instance.
Change your current connection to the SQL Server 2005 instance and your query should work.

N 56°04'39.26"
E 12°55'05.63"

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2011-04-01 : 08:13:06

I understand now.

it was the installation disk that only installed workstation components.

Now I have a clear idea what to do now.

Thank you for the reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources