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,
(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
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.