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

Posts   
 
    
JayBee
User
Posts: 278
Joined: 28-Dec-2006
# Posted on: 05-Jun-2023 16:15:41   

Hi,

I develop for dotnet 4.8 on a 2014 SQL Server database. I generate code for SelfServicing. For years I have been deploying the software to various windows server version and it runs without problems against SQL Server 2008 R2 databases.

Recently I had to change some software. In this situation the old software was running on Windows Server 2003 and the code was generated by version 2.6 of LLBLGen. I upgraded to version 5.7 of LLBLGen and regenerated the code. All runs well on my 2014 database (compatibility level is 2008 / 100) but in production I get an error: Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement. It has to do with a query on a typed view of which the results are shown in a GridView. The problem occurs when paging from page 1 to page 2

Any suggestions?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 05-Jun-2023 17:21:55   

Please check if the sql server compatibility level has been set correctly in production. We changed the default a while ago to SQL Server 2012+ (which will by default generate next offset queries). So if it's not set in the config file of your application or through code, it's by default sqlserver 2012+ and thus you get next offset queries. By changing it to 2005 you'll get the older style paging queries. See: https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_applicationconfiguration.htm#dqe-compatibility-mode-sql-server-only

Frans Bouma | Lead developer LLBLGen Pro
JayBee
User
Posts: 278
Joined: 28-Dec-2006
# Posted on: 05-Jun-2023 19:49:17   

It was not defined in production. After adding it, the problem was solved. Thanks for helping out.

I would like to note that the 5.10 documentation you referred to, suggest that this is not necesary. The text below the list of the different levels says: The default is 2 or SqlServer2005+ so omitting this tag will result in the SqlServer 2005+ compatibility mode

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 06-Jun-2023 08:09:52   

JayBee wrote:

It was not defined in production. After adding it, the problem was solved. Thanks for helping out.

I would like to note that the 5.10 documentation you referred to, suggest that this is not necesary. The text below the list of the different levels says: The default is 2 or SqlServer2005+ so omitting this tag will result in the SqlServer 2005+ compatibility mode

Ah, that is an error in the docs, we'll correct that, thanks!

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 07-Jun-2023 10:11:12   

Fixed

Frans Bouma | Lead developer LLBLGen Pro