No Support for OVER in MS SQL Server 2005?

Question

OVERI am getting an error when trying to run a query in MS SQL Server Management Studio. We use MS SQL Server 2005 on a windows 2003 server.

Here is the query:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
        FROM    dbo.[1_MAIN - Contacts]
        INNER JOIN
                dbo.Referral
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
        INNER JOIN
                dbo.prov_training_records
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
        LEFT OUTER JOIN
                dbo.Resource_Center
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
        FULL OUTER JOIN
                dbo.Providers
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q

When I run it I just get a pop up window with the following info:

Window Title - "Query Definitions Differ"

Description - "The following errors were encountered while parsing the contents of the SQL pane:"

Error - "The OVER SQL construct or statement is not supported."

Then at the end it says - "The query cannot be represented graphically in the Diagram and Criteria Pane."

However, I do not have the Diagram or Criteria panes open.

I need to make sure we have the correct syntax. If the syntax is correct then I either need to fix why the Management Studio throws this error or find a work-around to OVER and solve this with a different approach.

I know that the specs say "OVER" is supported, nevertheless I get the error.

In a new query window if I run this it works:

USE abtcontacts;
WITH    q AS
        (
        SELECT  dbo.[1_MAIN - Contacts].Contact_ID, dbo.[1_MAIN - Contacts].Date_entered_into_Database, dbo.[1_MAIN - Contacts].Date_of_Initial_Contact, 
                      dbo.[1_MAIN - Contacts].[Company_ Name], dbo.[1_MAIN - Contacts].Key_Contact_Title, dbo.[1_MAIN - Contacts].Key_Contact_First_Name, 
                      dbo.[1_MAIN - Contacts].Key_Contact_Middle, dbo.[1_MAIN - Contacts].Key_Contact_Last_Name, dbo.[1_MAIN - Contacts].Key_Credential, 
                      dbo.[1_MAIN - Contacts].Key_Contact_Occupation, dbo.[1_MAIN - Contacts].Key_Degree_1, dbo.[1_MAIN - Contacts].Key_Degree_2, 
                      dbo.[1_MAIN - Contacts].Key_Degree_3, dbo.[1_MAIN - Contacts].Date_of_Highest_Degree, dbo.[1_MAIN - Contacts].Work_Setting, 
                      dbo.[1_MAIN - Contacts].Website_Address, dbo.[1_MAIN - Contacts].Email_1_Key_Contact, dbo.[1_MAIN - Contacts].Email_2, 
                      dbo.[1_MAIN - Contacts].Email_3, dbo.[1_MAIN - Contacts].Day_Time_Phone_Number, dbo.[1_MAIN - Contacts].Extension, 
                      dbo.[1_MAIN - Contacts].Mobile_Phone_Number, dbo.[1_MAIN - Contacts].Bus_Fax_Number, dbo.[1_MAIN - Contacts].Home_Phone_Number, 
                      dbo.[1_MAIN - Contacts].Home_Fax_Number, dbo.[1_MAIN - Contacts].Mailing_Street_1, dbo.[1_MAIN - Contacts].Mailing_Street_2, 
                      dbo.[1_MAIN - Contacts].Mailing_City, dbo.[1_MAIN - Contacts].Mailing_State, dbo.[1_MAIN - Contacts].[Mailing_Zip/Postal], 
                      dbo.[1_MAIN - Contacts].Mailing_Country, dbo.[1_MAIN - Contacts].[Bad_Address?], dbo.[1_MAIN - Contacts].[PROV/REG?], 
                      dbo.[1_MAIN - Contacts].status_flag, dbo.[1_MAIN - Contacts].status_flag AS status_flag2, dbo.Providers.Referral_Source, dbo.Referral.Contact_Source, 
                      dbo.Resource_Center.cert_start_date, dbo.Resource_Center.cert_exp_date, dbo.prov_training_records.Contact_ID AS Expr2, 
                      dbo.prov_training_records.date_reg_email_sent, dbo.Resource_Center.access, dbo.Providers.Contact_ID AS Expr1,
                ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
        FROM    dbo.[1_MAIN - Contacts]
        INNER JOIN
                dbo.Referral
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
        INNER JOIN
                dbo.prov_training_records
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
        LEFT OUTER JOIN
                dbo.Resource_Center
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
        FULL OUTER JOIN
                dbo.Providers
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q
WHERE   rn = 1

Solving this problem will help me solve my other problem over here

Answer 1

Check the compatibility level on the particular database

There is some more detail @ http://msdn.microsoft.com/en-us/library/ms178653(v=SQL.90).aspx

If the compatibility level is less than 90, then new features, like the OVER clause, available only in SQL 2005, are turned off.

Answer 2

The problem is that you are using the Query Designer GUI. Stop doing that. The Query Designer is rubbish. It's useless for any non-trivial query development. Type you queries in a Management Studio editor window.

Answer 3

The documentation for SQL Server 2005 says that OVER is certainly supported.

The problem is that the editor window doesn't support it. See if you can close all the panes other than SQL and results view to make the error go away.

Answer 4

Have you tried prepending a semicolon in front of your With statement? (e.g. ;With q As)

---

Some general notes:

1. You must run this in a query not the designer. It sounds like you have fixed that.
2. Enumerate the columns instead of using *. I.e., in the example below replace Col1, Col2 with the actual columns you want prefixed by the table from whence they came.
3. Put use DatabaseName at the top of your script followed by a line break, followed by GO, followed by another line-break.
4. If the only statement prior to the With statement is the GO between your use statement and the query, then no semicolon is necessary since it is the first statement in the batch.

use DatabaseName
GO
WITH q AS
        (
        SELECT  TableName.Col1, TableName.Col2,...
            , ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID 
                                                        ORDER BY dbo.[1_MAIN - Contacts].Contact_ID ) AS rn
        FROM dbo.[1_MAIN - Contacts]
            INNER JOIN dbo.Referral
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
            INNER JOIN dbo.prov_training_records
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
            LEFT OUTER JOIN dbo.Resource_Center
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
            FULL OUTER JOIN dbo.Providers
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q