473,240 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

EXCEPT clause in SQL

I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense. The SQL Server books say it is supported, but why can't I use it in
VS?

Is there a better to tool to write queries in instead of VS? The query
editor seems very weak, much like MS Access' query builder.

I'm not sure which group to post this in, sorry if I'm in the wrong spot.
Aug 19 '08 #1
9 3418
What is the exact error message? It could be incorrect syntax, or the db
could be in a compatibility mode of a previous version.

For better help show us the code.

Better tool is SQL Server Management Studio - but don't use the visual
designers, learn to write SQL and you'll be much happier.

HTH
"Tyler S." wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense. The SQL Server books say it is supported, but why can't I use it in
VS?

Is there a better to tool to write queries in instead of VS? The query
editor seems very weak, much like MS Access' query builder.

I'm not sure which group to post this in, sorry if I'm in the wrong spot.
Aug 19 '08 #2
My application is for my checkbook register and a budgeting module. Here is
my SQL statement:
SELECT * FROM item_type WHERE item_id =
(SELECT item_id
FROM item_type
EXCEPT
SELECT budget.item_id
FROM budget WHERE month_id = @period );

Here is the text from the error:
"The EXCEPT SQL construct or statement is not supported."

When I run it with the parameter in the query, it gives me that msg and then
it says that I must declare the scalar variable and doesn't run. When I
enter the id instead of the parameter, it still gives me the error and then
runs the query. On other queries, it prompts me for the value of the
parameter but not this time.

Sometimes it won't run the query at all, it just gives me crap about the
except statement not being supported.

I don't use the visual query builder, I'm not strong in SQL but I do write
my own queries. I have a full version SQL Server 2005 and I don't have
Server Mgmt Studio, I thought it came with it. How do I get it and install
it?

Tyler

"KH" wrote:
What is the exact error message? It could be incorrect syntax, or the db
could be in a compatibility mode of a previous version.

For better help show us the code.

Better tool is SQL Server Management Studio - but don't use the visual
designers, learn to write SQL and you'll be much happier.

HTH
"Tyler S." wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense. The SQL Server books say it is supported, but why can't I use it in
VS?

Is there a better to tool to write queries in instead of VS? The query
editor seems very weak, much like MS Access' query builder.

I'm not sure which group to post this in, sorry if I'm in the wrong spot.
Aug 19 '08 #3
Syntax looks right so whatever db you're pointing at must not be MSSQL 2005.
Did you move the db from a prior version or something? Doing so will not
automatically upgrade it to the version of the server.

Sql Server does come with mgmt studio -- you have to install "tools" and it
should be in the start menu.
"Tyler S." wrote:
My application is for my checkbook register and a budgeting module. Here is
my SQL statement:
SELECT * FROM item_type WHERE item_id =
(SELECT item_id
FROM item_type
EXCEPT
SELECT budget.item_id
FROM budget WHERE month_id = @period );

Here is the text from the error:
"The EXCEPT SQL construct or statement is not supported."

When I run it with the parameter in the query, it gives me that msg and then
it says that I must declare the scalar variable and doesn't run. When I
enter the id instead of the parameter, it still gives me the error and then
runs the query. On other queries, it prompts me for the value of the
parameter but not this time.

Sometimes it won't run the query at all, it just gives me crap about the
except statement not being supported.

I don't use the visual query builder, I'm not strong in SQL but I do write
my own queries. I have a full version SQL Server 2005 and I don't have
Server Mgmt Studio, I thought it came with it. How do I get it and install
it?

Tyler

"KH" wrote:
What is the exact error message? It could be incorrect syntax, or the db
could be in a compatibility mode of a previous version.

For better help show us the code.

Better tool is SQL Server Management Studio - but don't use the visual
designers, learn to write SQL and you'll be much happier.

HTH
"Tyler S." wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense. The SQL Server books say it is supported, but why can't I use it in
VS?
>
Is there a better to tool to write queries in instead of VS? The query
editor seems very weak, much like MS Access' query builder.
>
I'm not sure which group to post this in, sorry if I'm in the wrong spot.
Aug 19 '08 #4
No, it's a new database and I don't have any other DB installed except for
Derby which comes with NetBeans 5 and 6.

I'll grab my .iso and see if I missed something when I installed, it's very
possible.

"KH" wrote:
Syntax looks right so whatever db you're pointing at must not be MSSQL 2005.
Did you move the db from a prior version or something? Doing so will not
automatically upgrade it to the version of the server.

Sql Server does come with mgmt studio -- you have to install "tools" and it
should be in the start menu.
"Tyler S." wrote:
My application is for my checkbook register and a budgeting module. Here is
my SQL statement:
SELECT * FROM item_type WHERE item_id =
(SELECT item_id
FROM item_type
EXCEPT
SELECT budget.item_id
FROM budget WHERE month_id = @period );

Here is the text from the error:
"The EXCEPT SQL construct or statement is not supported."

When I run it with the parameter in the query, it gives me that msg and then
it says that I must declare the scalar variable and doesn't run. When I
enter the id instead of the parameter, it still gives me the error and then
runs the query. On other queries, it prompts me for the value of the
parameter but not this time.

Sometimes it won't run the query at all, it just gives me crap about the
except statement not being supported.

I don't use the visual query builder, I'm not strong in SQL but I do write
my own queries. I have a full version SQL Server 2005 and I don't have
Server Mgmt Studio, I thought it came with it. How do I get it and install
it?

Tyler

"KH" wrote:
What is the exact error message? It could be incorrect syntax, or the db
could be in a compatibility mode of a previous version.
>
For better help show us the code.
>
Better tool is SQL Server Management Studio - but don't use the visual
designers, learn to write SQL and you'll be much happier.
>
HTH
>
>
"Tyler S." wrote:
>
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense. The SQL Server books say it is supported, but why can't I use it in
VS?

Is there a better to tool to write queries in instead of VS? The query
editor seems very weak, much like MS Access' query builder.

I'm not sure which group to post this in, sorry if I'm in the wrong spot.
Aug 19 '08 #5
Thanks, I'll try that. One of the great things about SQL is that the
multitude of ways you can write a query to get an answer, but it's hard to
remember all of them, especially for a beginner. This application is a great
learning tool for myself to get some experience.

"Peter Bromberg [C# MVP]" wrote:
INSTEAD OF "EXCEPT" try "WHERE NOT IN(....)
Peter
"Tyler S." <Ty****@discussions.microsoft.comwrote in message
news:84**********************************@microsof t.com...
Aug 19 '08 #6
Tyler S. wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense.
Is your database compatibility level set to "SQL Server 2000", by any
chance? This will be the case if you upgrade or restore a database
originally created by SQL Server 2000 (or earlier). The new language
features are disabled unless the compat level is raised.

--
J.
Aug 19 '08 #7
I'm new to SQL Server (I'ved used others before) so I don't know where to set
those options. If it's done with the Server Mgmt Studio, I didn't have that
installed. I'm trying to fix that now.

"Jeroen Mostert" wrote:
Tyler S. wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense.

Is your database compatibility level set to "SQL Server 2000", by any
chance? This will be the case if you upgrade or restore a database
originally created by SQL Server 2000 (or earlier). The new language
features are disabled unless the compat level is raised.

--
J.
Aug 19 '08 #8
I now know what you mean and no, it is set to 2005 (90) compatibility. For
some reason, not all of SQL Server installed a few months ago so I've
re-installed it and I now have the Management Studio.

"Jeroen Mostert" wrote:
Tyler S. wrote:
I have VS.Net with SQL Server 2005 and I'm developing a windows application
in C# and I'm trying to write a query to use an EXCEPT clause and I'm getting
an error saying the EXCEPT clause isn't supported and that doesn't make any
sense.

Is your database compatibility level set to "SQL Server 2000", by any
chance? This will be the case if you upgrade or restore a database
originally created by SQL Server 2000 (or earlier). The new language
features are disabled unless the compat level is raised.

--
J.
Aug 20 '08 #9
It turns out I didn't have a full installation of SQL Server. I had to
uninstall it twice before I got all components to load. For some reason the
Mgmt Studio wouldn't load before but now it's installed. I wish I had this
tool before!!! It was what I was looking for to be able to write queries
like I've been taught, no stupid visual query builder.

Now I don't get the error anymore!!!

"KH" wrote:
Syntax looks right so whatever db you're pointing at must not be MSSQL 2005.
Did you move the db from a prior version or something? Doing so will not
automatically upgrade it to the version of the server.

Sql Server does come with mgmt studio -- you have to install "tools" and it
should be in the start menu.
"Tyler S." wrote:
Aug 20 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: George Young | last post by:
I have a bunch of blanket "except:" clauses like: class DatabaseError(StandardError): pass class OperationalError(StandardError): pass try: stuff except _pg.error, msg: raise...
13
by: KefX | last post by:
This may have been discussed before, but I'm kind of confused as to why Python doesn't support having both an except ~and~ a finally clause, like this: try: raise RuntimeException except:...
9
by: David Stockwell | last post by:
In referring to my copy of the python bible, it tells me I can't use all three items 'try' except and finally. I can use the t/f or t/e combinations though What combination can i use if i want...
20
by: John Salerno | last post by:
I'm starting out with this: try: if int(text) 0: return True else: self.error_message() return False except ValueError: self.error_message()
35
by: Arnaud Delobelle | last post by:
Hi all, Imagine I have three functions a(x), b(x), c(x) that each return something or raise an exception. Imagine I want to define a function that returns a(x) if possible, otherwise b(x),...
2
by: AWasilenko | last post by:
I can't figure out this problem Im having, I just can't understand why it is ignoring the call I put in. First the code (This is a cherrypy website): import sys, cherrypy, html class Root:...
5
by: Nebur | last post by:
I'm using the contract.py library, running Python 2.4.4. Now I'm confronted with the following exception backtrace: (...) File "/usr/lib/python2.4/site-packages/contract.py", line 1265, in...
3
by: Adam W. | last post by:
I am trying to handle a Unicode error but its acting like the except clause is not even there. Here is the offending code: def characters(self, string): if self.initem: try:...
11
by: cnb | last post by:
If I get zero division error it is obv a poor solution to do try and except since it can be solved with an if-clause. However if a program runs out of memory I should just let it crash right?...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.