Hi,

i'm having problems with Tables and SqlSrv connection:

This is my function

 protected function onCreated()
    {
        $this->manageTable("Custtable")->inSource(Axapta::class);
    }

Axapta is a SQLServer connection:

use \koolreport\dashboard\sources\SQLServer;

class Axapta extends SQLServer
{
    protected function connection()
    {
        return [
            "connectionString"=>"sqlsrv:server=****;Database=****",
            "username"=>"alex",
            "password"=>"******",
            "charset"=>"utf8"
        ];
    }
}

And when I try to access this page, it throws this error:

Message: Query Error >> ["42000",102,"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '0'."] >> SELECT Custtable.ACCOUNTNUM, Custtable.NAMECOMER, Custtable.NAME, Custtable.weblogin, Custtable.PHONE, Custtable.zipcode FROM Custtable OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY || Sql params = [] || Search params = []

It seems that OFFSET command not works with this version of SQL Server but i don't know how can i fix it...

The reason this happens is because the query builder adds the OFFSET FETCH NEXT to the query even if there is no ORDER BY clause in the query. If you modify the file \koolreport\querybuilder\SQLServer.php and include the instructions to add the OFFSET inside the brackets of the orders condition, the problem will be gone. It is like this because OFFSET FETCH is a part of the ORDER BY instruction, cannot be used standalone like LIMIT in MySQL.

It shoul read like this: `

   if (count($this->query->orders)>0) {
        $sql.=" ORDER BY ".$this->getOrderBy($this->query->orders);
		
		if ($this->query->offset!==null) {
			$sql.=" OFFSET ".$this->query->offset." ROWS";
		}

		if ($this->query->limit!==null) {
			$sql.=" FETCH NEXT ".$this->query->limit." ROWS ONLY";
		}
    }