"Incorrect syntax near 'OFFSET'" modift sql comm 2012 to 2008

Question

I'm listing questions with this

SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, u.uFullname, qcat.qcatTitle, q.qId, q.qStatus 
FROM tblQuestion AS q INNER JOIN tblUser AS u 
ON q.uId = u.uId INNER JOIN tblQuestionCategory AS qcat 
ON q.qcatId = qcat.qcatId 
WHERE (q.qStatus = 1) 
ORDER BY q.qCreatedOn DESC 
OFFSET @page*10 ROWS FETCH NEXT 10 ROWS ONLY

But there is a problem in my server,

Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.

How can I modify my query for sql server 2008?

One more question. How can I write a stored procedure for listing pages? Here is my full of code http://codepaste.net/gq5n6c

Answer: http://codepaste.net/jjrkqr

Hayley

I have a hunch that (@page*10) might resolve it. If not, look into the docs to see the formal syntax. – Forenoon

I just tried it and the query works fine. Googling the message turns up some solutions, though. – Forenoon

it works fine but now in 2008. how will it work in 2008? – Hayley

You may also check sql-tutorial.ru/en/book_paging/page3.html – Croix

possible duplicate of SQL Server OFFSET equivalent – Kelseykelsi

Answer 1

As found out in the comments the reason for the error is because of the fact that SQL Server 2008 does not support it. You may try to change the query according to SQL Server 2012.

Something like this:-

SELECT column1
FROM   (
          SELECT column1, ROW_NUMBER() OVER (ORDER BY column_id) AS x
          FROM   mytable
       ) AS tbl
WHERE  tbl.x BETWEEN 20 AND 30

In your code:-

SELECT * FROM  
(SELECT ROW_NUMBER() OVER(ORDER BY q.qId) AS rownumber 
FROM tblQuestion AS q 
INNER JOIN tblUser AS u ON q.uId = u.uId 
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId ) as somex 
WHERE  somex.rownumber  BETWEEN 11 AND 20

---

The issue is because you have not defined @page.

Try this (As you have not mentioned what is @page. I am taking it as some constant or may be you can declare it and then set the value for it):-

declare @page int
set @page = 5  // You may set any value here.

SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, 
u.uFullname, qcat.qcatTitle, q.qId, q.qStatus 
FROM tblQuestion AS q 
INNER JOIN tblUser AS u ON q.uId = u.uId 
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId 
WHERE (q.qStatus = 1) 
ORDER BY q.qCreatedOn DESC 
OFFSET (@page*10) ROWS
FETCH NEXT 10 ROWS ONLY

Croix

no no. there is a misunderstanding. i have defined @page already. here is full off code. codepaste.net/gq5n6c – Hayley

here is output from sqlserver: codepaste.net/cpsdr6 i think its about sql server version.(its 2010 OFFSET not recognized) – Hayley

@tayfun;- I think that may be because ORDER BY Clause (Transact-SQL) this syntax is not supported in SQL Server 2008. – Croix

@tayfun:- Check this SQL Fiddle it is working fine for me :- sqlfiddle.com/#!6/10e8f/1 – Croix

@tayfun:- Also make sure that database compatibility level is set to SQL Server 2012 – Croix

@tayfun:- As in SQL Fiddle 2008 version I also receive the same error:- sqlfiddle.com/#!3/10e8f/1 – Croix

yes yes i know. my server have sql server 2008. how can i optimize my code for 2008? it works in 2012. should work in 2008 – Hayley

@tayfun:- Didnt get that very well. I mean this does not work in SQL SERVER 2008. You have to make your query compatible with that – Croix

i understand you. how can i make my query compatible for 2008? can you give me a query for 2008? – Hayley

@tayfun:- You may find this helpful:- #15564719 – Croix

i visited that link already. here is the problem. it not working codepaste.net/5zhrrr – Hayley

@tayfun:- You are welcome.! P.S. Dont you think that I deserve an upvote as well from you ;) – Croix

Answer 2

For people using Entity Framework, particulary database first, this error can occur if you develop with SQL 2012 but deploy to an earlier version.

The problem will occur if you use Take...Skip functionality, as SQL 2012 has a new syntax for this. See:

http://erikej.blogspot.co.uk/2014/12/a-breaking-change-in-entity-framework.html

The fix is to edit your .edmx file and change the ProviderManifestToken value from 2012 to your database version, e.g. 2008.

Avivah

Answer 3

I encountered this when using Entity Framework. I was developing on a machine with SQL Server 2012. But deployed on a machine with SQL Server 2008. Instead of doing a skip and take on the query, I did a ToList() on the query and did a skip/take on that ToList() in memory. Not ideal, but at least it will work.

Fango

You will hate this solution when you have so many records though. – Grosbeak

Answer 4

When working in a team with multiple versions off Sql Server the model Edmx project changes the property : ProviderManifestToken.

I solved the problem by changing it back to my Version.

Martella