2005 v 2000 - 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 2005 Forums

Transact-SQL (2005)

2005 v 2000

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-03 : 10:35:35

I have remote access to our data stores which are held in SQL SERVER 2000. On the client side I am using SQL Server 2005. I am curious about some of the new functionality in 2005 such as Common Table Expressions. I receive error

quote:

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

when attempting to use CTE. Is it because the server is SQL Server 2000?

Here is a simple example of what I was doing:


WITH prop_cte ( id, parcel_number, alt_parcel_nr, pact_code )
AS
(
    select id, parcel_number, alt_parcel_nr, pact_code 
    from AscendWheelerMain..property 
    where eff_to_date is null
)

select p.*, pv.*
from
    prop_cte p INNER JOIN
    AscendWheelerMain..prop_valuation pv ON
    p.id = pv.property_id
        and pv.tax_year = 2009
        and pv.taxable_ind = 'Y'

I believe the syntax is correct...

Education is what you have after you've forgotten everything you learned in school

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 10:39:08

It wont work in versions prior to 2005

Madhivanan

Failing to plan is Planning to fail

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-03 : 10:44:13

I knew it was new with 2005. Evidently the script is being run on the remote server and not from within my instance of 2005?

Education is what you have after you've forgotten everything you learned in school

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 10:46:38

hi doco,

Yes -- whenever you run any sql through management studio all the work is actually done at the database server -- if the server is 2000 then you can't use any 2005 extensions / new features.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

doco
Yak Posting Veteran

77 Posts

Posted - 2010-06-03 : 10:48:37

Thanks...

Education is what you have after you've forgotten everything you learned in school

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources