CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduressql-server-ce
Simon Kiely
Simon Kiely

Reputation: 6050

Error parsing query in SQL Server Compact

I am running my query using WebMatrix, I think it is correct, however I am getting an error.

My query is as follows :

   var result = db.Query(
                @"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring
                FROM   event e
                JOIN   Membership m ON m.GroupID = e.group_id
                WHERE  e.recurring = 0
                AND    m.UserID = @0
                AND    e.event_start >= @1
                AND    e.event_end <= @2
                UNION ALL
                SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start), DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring
                FROM   event e
               JOIN   Membership m ON m.GroupID = e.group_id
               CROSS JOIN 
                ( SELECT  row_number() OVER (ORDER BY Object_ID) AS weeks
                  FROM SYS.OBJECTS
                ) AS w
                WHERE  e.recurring = 1
                AND    m.user_id = 4;
                AND    e.event_start >= @4
                AND    e.event_end <= @5", userID, start, end, userID, start, end
            );

The error is :

System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 10,Token line offset = 38,Token in error = OVER ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()

I'm holding query in a .cs file. I'm told I should create a stored procedure since SQL Server compact may not support the OVER function, is this correct? How can I create a stored procedure for this query?

Upvotes: 0

Views: 1077

Answers (2)

STO
STO

Reputation: 10658

Seems ROW_NUMBER and other windowed functions are not supported by Sql Server CE 4.0 :(

Upvotes: 0

Andomar
Andomar

Reputation: 238276

After FROM, you can list multiple tables separated by comma's, and the result will be a CROSS JOIN of all those tables.

But you can't use the comma syntax after a JOIN. Instead, write out the CROSS JOIN:

JOIN   Membership m 
ON     m.GroupID = e.group_id
CROSS JOIN 
       (
       SELECT  row_number() OVER (ORDER BY Object_ID) AS weeks
       FROM SYS.OBJECTS
       ) AS w

Upvotes: 1

Related Questions