Issue executing stored procedure from PHP to a Microsoft SQL SERVER

Question

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

Suhail · Accepted Answer · 2015-07-10 10:35:56Z

4

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

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

edited Jul 10, 2015 at 10:35

answered Apr 30, 2015 at 10:39

Suhail

564 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lymuel Over a year ago

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

Ulises Burlando · Accepted Answer · 2015-05-24 15:36:25Z

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

answered May 24, 2015 at 15:36

Ulises Burlando

312 bronze badges

1 Comment

MarcM Over a year ago

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

Rashy · Accepted Answer · 2015-05-14 06:48:18Z

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 =]

answered May 14, 2015 at 6:48

Rashy

91110 silver badges14 bronze badges

Comments

Ranjeet Kumar · Accepted Answer · 2015-12-19 12:38:57Z

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);

answered Dec 19, 2015 at 12:38

Ranjeet Kumar

113 bronze badges

Collectives™ on Stack Overflow

Issue executing stored procedure from PHP to a Microsoft SQL SERVER

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related