Edit file "/system/database/drivers/sqlsrv/sqlsrv_driver.php", Line 575:
function _limit($sql, $limit, $offset) { if($offset === FALSE || version_compare($this->version(), "9.0.00", "<=")) { // do simple limit if no offset, or version is earlier than 2005 $i = $limit + $offset; return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); } else { if (version_compare($this->version(), "11.0.00", ">=")) { $orderBy = "ORDER BY "; $orderBy .= implode(', ', $this->ar_orderby); if ($this->ar_order !== FALSE) { $orderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC'; } else { $orderBy .= '1 ASC '; } // for Denali's new feature if ($this->ar_orderby !== FALSE) return $sql . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; else return $sql . $orderBy . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; // for Denali's new feature // return $sql . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; } else { $orderBy = "ORDER BY "; $orderBy .= implode(', ', $this->ar_orderby); if ($this->ar_order !== FALSE) { $orderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC'; } $sql = preg_replace('/(\\'. $orderBy .'\n?)/i','', $sql); $sql = preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 row_number() OVER ('.$orderBy.') AS CI_offset_row_number, ', $sql); $newSQL = "SELECT * \nFROM (\n" . $sql . ") AS A \nWHERE A.CI_offset_row_number BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")"; return $newSQL; } } }
