Intersect and Except returning error

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)

Intersect and Except returning error

Author

Topic

Rammi
Starting Member

19 Posts

Posted - 2007-12-19 : 01:52:56

Following query returns error when used on different servers.

Query:

Select sourceid from
OPENDATASOURCE('SQLOLEDB', 'DATA SOURCE=main;USER ID=user;PASSWORD=user').SOURCE.DBO.sourceprofile
intersect
select mip_source_id
from OPENDATASOURCE('SQLOLEDB', 'DATA SOURCE=source;USER ID=user;PASSWORD=user').Source.DBO.sources

Error:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'intersect'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 02:58:41

Check the compatibility level of your database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 03:18:59

And make sure you have enabled "ad hoc queries" in Surface Area Configuration.

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-12-19 : 05:10:21

ad hoc queries are enabled

quote:
Originally posted by Peso

And make sure you have enabled "ad hoc queries" in Surface Area Configuration.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 05:47:33

And the answer to Harsh's question?

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-12-19 : 23:02:06

compatibility level is 80 for my databases

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-20 : 03:03:08

That's the reason you get error. Your server is set to be compatible with SQL Server 2000 which does not support INTERSECT and EXCEPT.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources