3

I am using the code igniter framework. And I been trying to execute a stored procedure(no params) I wrote on Microsoft SQL Server 2008 from PHP but i get an error . This stored procedure runs good on Microsoft SQL Server with no errors. I am using sqlsrv as the driver and I have a PHP version 5.2 if that helps.

This is the error I get 

Error Number: 01000
[Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor
Exec sp_test

The following is part of the code I have 

function index(){
  $this->load->database();
  $query=$this->db->query("Exec sp_test");
  var_dump($query->result());
}

I replace the actual query with an actual query and it does work but not with a sp call. Any help will be appreciated, I tried for so long on this and i can't get it to work. Thanks

user3547086
  • 119
  • 1
  • 3
  • 12

4 Answers4

4

I had faced the same issue. Removing the 'EXEC' worked for me.

$query = $this->db->query("procedure_name parameter_1, parameter_2");

Suhail
  • 56
  • 3
  • 1
    hello sir, i followed your solution but still no luck when calling my stored procedure which accepts 4 parameters, but when i run the stored procedure on mssql query window it does have data to be returned **return $this->db->query(" pr_proc_1 153, 1, 'N', 'N' ")->result();**. it returns "No Content" with status 204 – Lymuel Jun 21 '17 at 11:37
3

I've been stumbling with a similar error for some time. The thing is that the execution of the stored rpocedure returned a state code 01000/0, which is not an error but a warning, and still don't know why it's beeing returned.

Anyway, the thing is that the SP was a simple select, and whenever I runned it with the query tool, the select worked just fine, even invoking this select from php with codeigniter the values where returned correctly, but when I tried to use the stored procedure, it keep failing.

Finally got it working and the solution was to simply mark to not return warnings as errors. Here's a sample code:

 $sp = "MOBILE_COM_SP_GetCallXXX ?,?,?,?,?,?,?,?,?,? "; //No exec or call needed

 //No @ needed.  Codeigniter gets it right either way
 $params = array(
            'PARAM_1' => NULL,
            'PARAM_2' => NULL,
            'PARAM_3' => NULL,
            'PARAM_4' => NULL,
            'PARAM_5' => NULL,
            'PARAM_6' => NULL,
            'PARAM_7' => NULL,
            'PARAM_8' => NULL,
            'PARAM_9' => NULL,
            'PARAM_10' =>NULL);

 //Here's the magic...
 sqlsrv_configure('WarningsReturnAsErrors', 0);

 //Even if I don't make the connect explicitly, I can configure sqlsrv
 //and get it running using $this->db->query....

 $result = $this->db->query($sp,$params);

That's it. Hope it helped

Ulises Burlando
  • 31
  • 2
  • Similar case happened to me in a test platform (not in other platforms!). But not with a stored procedure but with a simple 'UPDATE'. Probablly due to sql_server config. Not being able to change config, this solution has been a great work-around. T – MarcM Jun 16 '17 at 11:42
1

I also had the similar issue while connecting to MS SQL Server 2008 using sqlsrv driver. My issue was resolved using the following code:

$result = $this->db->query("PROCEDURE_NAME {$param_1}, {$param_2}")
                   ->result_array();

I have this helps someone out there =]

Rashy
  • 893
  • 9
  • 12
1

I am facing fetch data from sql server stored procedure in codeignitor but finally resolved it.

    $sync= $this->load->database('sync', TRUE);
    $query = $sync->query('PROCEDURE_NAME')->result_array();
    echo '<pre>';
    print_r($query);
Ranjeet Kumar
  • 11
  • 3