SQL 2012 ORDER BY list of RANGE window frame cannot contain expressions of LOB type

I ran into this error when preparing some data for a report in SQL Server Reporting Services 2012. I was trying to partition data showing construction progress for work areas by week number using the updated ORDER BY clause for Windowing functions with SQL Server 2012.

This is the query:
SELECT Area, SubArea,
COUNT(SubArea) OVER
(PARTITION BY MAX(DATEPART(wk, PlannedComplete))
ORDER BY SubArea)

And this is the error I was getting:

“ORDER BY list of RANGE window frame cannot contain expressions of LOB type.”

Let’s break down the error message.

ORDER BY list
Something is wrong with the ORDER BY
RANGE window frame
Identifies the section after the Partition command
cannot contain expressions of LOB type
LOB or Large Object are Column definitions inc. Text, varchar(MAX), Image etc

Answer:
In this case it turned out that the application developers had specified that the SubArea column specification type was varchar(MAX)

Changing the SubArea column type to a more appropriate varchar(30) (Max length submission from the application is 30) resolves the issue.

Searching online for the exact error failed to bring up anything immediately useful to me so I’m putting this here for my reference, I hope it helps.

This entry was posted in Microsoft, SQL Server. Bookmark the permalink.

3 Responses to SQL 2012 ORDER BY list of RANGE window frame cannot contain expressions of LOB type

  1. You’re as bad as me, not blogging for a year 😀

  2. Niall says:

    I know, been putting it off but this error was bugging me so much I had to write it down.

    Niall

  3. Muhammad Ahmed says:

    Thanks for great explanation and it works for me !!! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *