[El Forum]

[eluser]Rav3soul[/eluser]
The "_limit" function didn't work, so I modified the "mssql_driver.php" file to make it work. Check my changes and you can use it to make a new release.
Basically, it's just to modify "system/database/drivers/mssql/mssql_driver.php"
to

Code:

function _limit($sql, $limit, $offset)
{
// Horrible solucion =)
//$i = $limit + $offset;
//return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);        
$cursorName = 'mi_cursor_'.md5(strtolower($sql));
$sql = "DECLARE ".$cursorName." CURSOR DYNAMIC READ_ONLY FOR
    ".$sql."
    OPEN ".$cursorName."
    DECLARE @CURSOR AS INT
    SELECT @CURSOR = CURSOR_HANDLE FROM  MASTER.DBO.SYSCURSORS
    WHERE CURSOR_NAME = '".$cursorName."'
    EXEC SP_CURSORFETCH @CURSOR, 32, ".($offset+1).", ".$limit."
    EXEC SP_CURSORCLOSE @CURSOR";
    return $sql;
}

Yeah, I know: "It's a Cursor!", but this method not use "ORDER BY". I find this method here :
http://www.elguille.info/colabora/NET200...Server.htm
And I just modify this to work whit CodeIgniter.
I use this, and I find this method "the better"

I've been waiting for your comments.

[El Forum]

[eluser]vicman[/eluser]
you could to put an example?

Thanks!!

[El Forum]

[eluser]Rav3soul[/eluser]
Just modify: system/database/drivers/mssql/mssql_driver.php and load (for example) a scaffolding from any table.

[El Forum]

[eluser]barbazul[/eluser]
hola Rav3soul,

Parece una solución viable, pero estás seguro de que anda en cualquier versión de MSSQL?

Yo no lo puedo probar por no tener a mi disposicion un servidor de MS, pero recuerdo un proyecto en el que tener esta solucion me hubiera salvado la vida.
No me queda claro por que no funcionaria con ORDER BY

Saludos

----------------------------------------------------------------

hi Rav3soul,

Looks like a good solution, but are you sure it will work on any version of MSSQL?

I cannot test it since I don't have a MS server, but I once worked on a project on MS, and this solution would have saved my life.
I still dont understand why it cant work with ORDER BY though

[El Forum]

[eluser]Rav3soul[/eluser]
Hola,
Esta solución no ordena por ningun criterio, muestra los campos como si se hiciera un simple
"SELECT * FROM TABLA", desde el Query Analyzer

Sin embargo, la solución de ORDENAR Id's o campos normales, realmente sería engorrosa (dificil) de implementar cuando tus tablas tengan mas de 1 identificador o llave primaria y peor aun... si que no las tuviera.

Lo probé en MSSQL 2000 y funciona de maravilla =).

Ahora puedes revisar el link que esta ahí.
http://www.elguille.info/colabora/NET200...Server.htm

[El Forum]

[eluser]barbazul[/eluser]
I' reading through the example page and found the following example.
I'm not an expert in MSSQL but it looks as if they are using an order by clause without any trouble.
Maybe there is a way to accomplish this after all

this is the kind of thing for which I hate working with mssql

----------------------------------------------------------

Estoy leyendo la pagina del link y encontre este ejemplo.
Ahora, yo no soy un experto en MSSQL pero parece que en el ejemplo usan un order by sin problemas

Este es el tipo de cosas por las que odio trabajar con mssql

(traduje el codigo al ingles para entendimiento general)

----------------------------------------------------------

Code:

CREATE PROCEDURE GetContactsPage
    @PageSize int,
    @PageNumber int
AS
    DECLARE @Handle int
    DECLARE @CursorType  int
    SET @CursorType=4 -- Forward only
    DECLARE @LockType int
    SET @LockType =1 -- Read only
    DECLARE @Query nvarchar(4000)
    SET @Query = N'SELECT ContactID, LastName, FirstName, EmailAddress, Phone
                      FROM Person.Contact ORDER BY LastName, ContactID'
    DECLARE @rowcount  int -- will always be -1
    EXEC sp_cursoropen @Handle OUTPUT, @Query, @CursorType OUTPUT, @LockType OUTPUT, @rowcount OUTPUT

    DECLARE @DisplacementType int
    SET @DisplacementType = 32 -- Relative
    DECLARE @FirstRecord int
    SET @FirstRecord = @PageSize * @PageNumber + 1
    EXEC sp_cursorfetch @Handle, @DisplacementType, @FirstRecord , @PageSize

    EXEC sp_cursorclose @Handle

[El Forum]

[eluser]vicman[/eluser]
muchas gracias, barbazul

[El Forum]

[eluser]mr.zeno[/eluser]
[quote author="Rav3soul" date="1185508771"]The "_limit" function didn't work, so I modified the "mssql_driver.php" file to make it work. Check my changes and you can use it to make a new release.
Basically, it's just to modify "system/database/drivers/mssql/mssql_driver.php"
to

Code:

function _limit($sql, $limit, $offset)
{
// Horrible solucion =)
//$i = $limit + $offset;
//return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);        
$cursorName = 'mi_cursor_'.md5(strtolower($sql));
$sql = "DECLARE ".$cursorName." CURSOR DYNAMIC READ_ONLY FOR
    ".$sql."
    OPEN ".$cursorName."
    DECLARE @CURSOR AS INT
    SELECT @CURSOR = CURSOR_HANDLE FROM  MASTER.DBO.SYSCURSORS
    WHERE CURSOR_NAME = '".$cursorName."'
    EXEC SP_CURSORFETCH @CURSOR, 32, ".($offset+1).", ".$limit."
    EXEC SP_CURSORCLOSE @CURSOR";
    return $sql;
}

Yeah, I know: "It's a Cursor!", but this method not use "ORDER BY". I find this method here :
http://www.elguille.info/colabora/NET200...Server.htm
And I just modify this to work whit CodeIgniter.
I use this, and I find this method "the better"

I've been waiting for your comments.[/quote]


This works for me, why are alternatives given? (is there something bad about this method?)

[El Forum]

[eluser]Unknown[/eluser]
I've replaces the mssql_driver _limit code with your code but its not working. Is there any other setting required for it to working?