1

Just installed Microsoft SQL Server Management Studio 2012 today. In familiarizing myself with the pagination feature addition of ORDER BY, I keep running into this error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'OFFSET'.
Msg 153, Level 15, State 2, Line 6
Invalid usage of the option NEXT in the FETCH statement.

Here is my query:

    SELECT SingleWomansName, NumberOfCats
    FROM CatLadies
    WHERE NumberOfCats > 1
    ORDER BY NumberOfCats
    OFFSET 10 ROWS
    FETCH NEXT 5 ROWS ONLY

I've seen plenty of how-to articles with similar syntax. What gives?

http://msdn.microsoft.com/en-us/library/gg699618.aspx

sarnold
  • 102,305
  • 22
  • 181
  • 238
Jonny Lawrence
  • 109
  • 2
  • 6
  • 2
    You said you installed SSMS 2012 today. Are you also connecting to a 2012 SQL server? As this syntax is new to 2012, if you connect to a 2008 (etc) instance, it won't work. – Jon Egerton Jun 30 '12 at 00:15
  • 3
    For others who are certain they are querying a 2012 instance and still getting the error, the order by clause is required. – dudeNumber4 Mar 12 '13 at 21:52

2 Answers2

3

Are you positive you are connected to a SQL Server 2012 database? The following works:

Select Name, CatCount
From CatLadies
Order By Name
Offset 2 Rows 
Fetch Next 2 Rows Only

SQL Fiddle version

I do get the error you are seeing when I switch to SQL Server 2008

SQL Fiddle using SQL Server 2008

Thomas
  • 63,911
  • 12
  • 95
  • 141
1

Make sure your database compatibility level is set to SQL Server 2012 (110).

Guang Zhao
  • 11
  • 1