pagination with sqlsrv driver

[crisleiria]

Dear all,

I haven't found any solution yet, so if anyone has this problem let me know.

I have just upgrade laravel from 6 to 8 on all my laravel main production projects. But one detail was not is the docs, and this have big impact on my case.

I have SQL SERVER 2008 R2 on production and after update Laravel I start seing this error when eloquent is doing pagination:

General error: 20018 Invalid usage of the option next in the FETCH statement

I understand the problem, SQL SERVER has new functions to paginate after SQL SERVER 2012 and it seems that eloquent after laravel 7 use this new SQL function FETCH and NEXT.

This error appears when using this code:

$list = $query->skip($listsettings->page) ->take($listsettings->records) ->get();

I believe that will also occur on paginate() method (not tested yet).

There is any option on the database.php file, specific to sqlsrv driver to implement the SQL SERVER 2008 pagination functions?
Upgrade SQL SERVER is not an option.. at least for now.

This detail should be in the upgrade docs I belieave, it has low impact, but it should be.

Thank in advance,
Cristovao Oliveira

[gmutinel]

Have you tried using

$query->offset($offset)->limit($limit)->get();

or the newer

$query->forPage($page, $limit)->get();

[crisleiria]

Yes, same error.

skip and take are alias for offset and limit.

forPage (didnt know this one) same error.

[gmutinel]

Ok, then I think that your problem was introduced here: beea2aa

In the PR discussion there are some tricks to bypass this: #39863

As they say in the same thread, Laravel officially supports SQL Server 2017+ (I have projects in Production with L9 + SQL Server 2012 and they work perfectly btw) as stated here: https://laravel.com/docs/8.x/database#introduction

[crisleiria]

Thank you @gmutinel for pointing me on the rigth direction,

It is working now.

I have implemented this solution (for laravel 5 but still applies on current Laravel versions):
https://medium.com/@daniilromazanov/how-to-extend-query-grammar-in-laravel-fb3d2d6de6d4

I also have connections to mysql, so I needed to overrite only driver sqlsrv.

Laravel allows to overrite grammar class. Simple and elegant solution (As always on Laravel).

[gmutinel]

Glad you worked it out! Might worth a PR to 9.x with a couple o ifs on the SQL server version?

[crisleiria]

I believe not.. this solution is the best.

We will eventually update SQL server (when we have budget, this update is very expensive)