Why does EXCEPT not work? Incorrect Syntax near the Word Except

sql sql-server tsql sql-server-2008 except
bizness picture bizness · Nov 2, 2010 · Viewed 11.9k times · Source 
SELECT SKU
FROM PartProductSupplemental
EXCEPT
SELECT SKU
FROM Inventory

Why do I get this error:

Incorrect Syntax near the Word Except

I check on line and syntax is syntactically correct:

SELECT ProductID 
FROM Production.WorkOrder
EXCEPT
SELECT ProductID 
FROM Production.Product

Answer

Vidar Nordnes picture Vidar Nordnes · Nov 2, 2010

Your database compatibility mode is probably set to 2000 (80) or earlier.

In Management Studio:

  1. Right click on the database name under the "Databases" heading in the Object Explorer
  2. In the Properties window that pops up, select "Options" - Compatibility Level is third from the top, on the right.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More