Laravel Update break pagination

[henryavila]

• Laravel Version: 8.75.0
• PHP Version: 8.0
• Database Driver & Version: Sql Server 2008

Description:

The recent Laravel upate break my entire Laravel Nova pagination.
I know SQL Server 2008 is not officially supported, we are planniing upgrade it, BUT with this release, all pagination (and my) are completely broken. It add a great breaking change!. Please, revert it.

On all pagination, I have the error:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Sintaxe incorreta próxima a 'offset'. (SQL:
 select * from xxxxxxxxxxxxxxxxxxxx  order by [xxxxxxx].[id] desc offset 30 rows fetch next 31 rows only)

Related PR

#39863

Steps To Reproduce:

Use Laravel Nova, SqlServer 2008 and install the last Laravel Version. All pagination will be broken.

[fredsal]

Laravel 8 officially supports only SQL Server 2017+, meaning that it's not officially guaranteed that Laravel 8 will work on anything below that so this is not a breaking change (as all officially suported SQL server versions work fine with this change) and basically it's pure luck that your Laravel 8 app worked with SQL Server 2008 at all until now.

[DarkGhostHunter]

@henryavila overwrite SqlServerGrammar.php with your own custom grammar(old version of the file) on providers, or rollback to old laravel framework version till you upgrade your database
app('db.connection')->setQueryGrammar(new CustomGrammar())

[driesvints]

I'm sorry but we don't support SqlServer 2008. Please upgrade to 2017 or preferable the latest one.

[vedmant]

I have the same problem and I can't upgrade to newer SqlServer, what is the solution to keep it working with the older SqlServer?

[PaolaRuby]

@vedmant there is the solution, can't you read it?

[vedmant]

@PaolaRuby Can you point to to exact details of the solution?

[PaolaRuby]

Here, above, there are not so many comments to make it so difficult to read it
On AppServiceProvider app('db.connection')->setQueryGrammar(new CustomGrammar())

[vedmant]

Ok, here is a solution if someone is searching:

<?php

namespace App\Database;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\SqlServerGrammar as QueryGrammar;

class SqlServerGrammar extends QueryGrammar
{
    /**
     * Compile a select query into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @return string
     */
    public function compileSelect(Builder $query)
    {
        if (! $query->offset) {
            return parent::compileSelect($query);
        }

        // If an offset is present on the query, we will need to wrap the query in
        // a big "ANSI" offset syntax block. This is very nasty compared to the
        // other database systems but is necessary for implementing features.
        if (is_null($query->columns)) {
            $query->columns = ['*'];
        }

        return $this->compileAnsiOffset(
            $query, $this->compileComponents($query)
        );
    }
}

In AppServiceProvider.php

    public function register()
    {
        Connection::resolverFor('sqlsrv', function ($connection, $database, $prefix, $config) {
            return new SqlServerConnection($connection, $database, $prefix, $config);
        });
    }

Add custom SqlServerConnection.php with override:

    protected function getDefaultQueryGrammar()
    {
        return $this->withTablePrefix(new SqlServerGrammar);
    }