EXCEPT clause in SQL

[=?Utf-8?B?VHlsZXIgUy4=?=]

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.

[=?Utf-8?B?S0g=?=]

RE: EXCEPT clause in SQL

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.

[=?Utf-8?B?VHlsZXIgUy4=?=]

RE: EXCEPT clause in SQL

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.

[=?Utf-8?B?S0g=?=]

RE: EXCEPT clause in SQL

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.

[=?Utf-8?B?VHlsZXIgUy4=?=]

RE: EXCEPT clause in SQL

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.

[=?Utf-8?B?VHlsZXIgUy4=?=]

Re: EXCEPT clause in SQL

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." <TylerS@discuss ions.microsoft. comwrote in message
news:84D46466-C06D-4F66-B8B8-2FC4822F61EE@mi crosoft.com...

[Jeroen Mostert]

Re: EXCEPT clause in SQL

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.

[=?Utf-8?B?VHlsZXIgUy4=?=]

Re: EXCEPT clause in SQL

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.
>

[=?Utf-8?B?VHlsZXIgUy4=?=]

Re: EXCEPT clause in SQL

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.
>

[=?Utf-8?B?VHlsZXIgUy4=?=]

RE: EXCEPT clause in SQL

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