CODEWITHSUNDEEP
phpsql-serverpdo
jfreak53
jfreak53

Reputation: 2359

PDO with MSSQL returns Invalid Cursor

I'm running a connection with PDO to a local MSSQL database. Running any Stored procedure at all through the connection doesn't give me any error at all.

This one single Stored procedure is giving me the following error:

Error in SQL: [Microsoft][SQL Server Native Client 10.0]Invalid cursor state - Query: exec sp_Get_SaldosWeb @Tipo=1, @IdDato=15368

This is my current PDO string for connecting inside this function and returning the array:

        $query = $this->db->prepare($qry, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
        $final = $query->execute();
        $this->setRows($query);
        if(!$final) {
            $this->error($qry, $query, $ret);
        } else {
            return $query->fetchAll();
        }

I've tried closing the cursor both before the execute and after fetchAll like so:

    $rows = $query->fetchAll();
    $query->closeCursor();
    return $rows;

But that also doesn't work. FYI, there are no queries executed before this call to this stored procedure anywhere in my code.

Not sure why this stored procedure is giving so many errors. If I run this exact same SP from MSSQL Management console it runs fine and returns 3 rows.

EDIT:

Here is the stored procedure:

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Get_SaldosWeb]          
@Tipo int ,  --1 = Alumno 2 = Familia          
@IdDato int           

as          


 if @Tipo = 1           
 begin          

  select SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos as [Nombre],          
   SC_Moneda.Nombre as Moneda, upper(replace(replace(replace(replace(replace( replace(lower(SC_TipoCargo.Nombre),char(237),'i'), char(243),'o'), char(233),'e') , char(225),'a') ,char(250),'u'),char(241),'ñ'))  as [Tipo de Cargo]            
  , cast(round(Sum(SC_CargoxAlumno.Debe),2) as decimal(18,2)) as Monto      ,    
  SC_Alumno.Codigo as Codigo2    
  from SC_CargoxAlumno          
  inner join SC_Moneda on SC_CargoxAlumno.IdSC_Moneda = SC_Moneda.IdSC_Moneda          
  inner join SC_TipoCargo on SC_CargoxAlumno.IdSC_TipoCargo = SC_TipoCargo.IdSC_TipoCargo          
  inner join SC_Alumno on SC_Alumno.IdSC_Alumno = SC_CargoxAlumno.IdSC_Alumno           
  inner join SC_Familia on SC_Alumno.IdSC_Familia = SC_Familia.IdSC_Familia          
  where          
  SC_Alumno.IdSC_Alumno = @IdDato          
  and SC_CargoxAlumno.Debe <> 0          
  group by           
  SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos ,          
   SC_Moneda.Nombre , SC_TipoCargo.Nombre        

 end          
 else          
 begin          

  select SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos as [Nombre],          
   SC_Moneda.Nombre as Moneda, upper(replace(replace(replace(replace(replace( replace(lower(SC_TipoCargo.Nombre),char(237),'i'), char(243),'o'), char(233),'e') , char(225),'a') ,char(250),'u'),char(241),'ñ'))  as [Tipo de Cargo]  ,          
  cast(round(Sum(SC_CargoxAlumno.Debe),2) as decimal(18,2)) as Monto      ,    
  SC_Alumno.Codigo as Codigo2    
  from SC_CargoxAlumno          
  inner join SC_Moneda on SC_CargoxAlumno.IdSC_Moneda = SC_Moneda.IdSC_Moneda          
  inner join SC_TipoCargo on SC_CargoxAlumno.IdSC_TipoCargo = SC_TipoCargo.IdSC_TipoCargo          
  inner join SC_Alumno on SC_Alumno.IdSC_Alumno = SC_CargoxAlumno.IdSC_Alumno           
  inner join SC_Familia on SC_Alumno.IdSC_Familia = SC_Familia.IdSC_Familia          
  where          
  SC_Familia.IdSC_Familia = @IdDato          
  and SC_CargoxAlumno.Debe <> 0          
  group by           
  SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos ,          
   SC_Moneda.Nombre , SC_TipoCargo.Nombre        

 end

Upvotes: 2

Views: 880

Answers (1)

bummi
bummi

Reputation: 27385

Add "SET NOCOUNT ON" to the beginning of your procedure.

You might find references here :

PHP Data Objects

My stored procedure "best practices" checklist

Upvotes: 3

Related Questions