MS SQL Query failed in PHP but not in MS SQL Server Management Studio

Question

I execute this query with php and odbc driver

$sql="DECLARE @Auftrag int; 
DECLARE @date_now datetime = getdate(); 
EXEC @Auftrag=EHS.dbo.SP_ANZEIGE 
@Tablet=1, 
@Status=0, 
@KuNr='K015538'; 
SELECT 'generatedID'=@Auftrag;";

$res = odbc_exec($db1_link, $sql) or die(odbc_errormsg()); // returns resource(13)
$firstRow = odbc_fetch_array($res); // dies error

If i do odbc_fetch_array the error "No tuples available at this result index" is thrown.

If I run the exact same query in Management Studio everything works fine. It shows me the computed generatedID. What is the difference?

greets Alex

Try to prefix the query with: set nocount on. That prevents SQL Server from sending rowcount updates, which UNIX clients sometimes mistake for actual rowsets. — Andomar
– Andomar, Commented Nov 4, 2013 at 9:30
WOW ... set nocount on really did it. I don't know exactly why but I'm really happy :) thanks — alex
– alex, Commented Nov 4, 2013 at 9:34

Andomar · Accepted Answer · 2013-11-04 10:04:46Z

2

Try to prefix the query with:

set nocount on

That prevents SQL Server from sending rowcount updates, which UNIX clients sometimes mistake for actual rowsets

answered Nov 4, 2013 at 10:04

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Comments

dharanbro · Accepted Answer · 2015-08-19 09:45:51Z

I had this same problem. In my case I was executing like this

$sql = "SELECT * FROM table1";
$resultSet = odbc_exec($sqllink, $sql);

while ($data = odbc_fetch_array($resultSet)) {
    $sql = "SELECT * FROM table2";
    $resultSet2 = odbc_exec($sqllink, $sql);//failed here
    while ($data2 = odbc_fetch_array($resultSet2)) {

           //something here

    }
}

and I changed like this and it worked

$sql = "SELECT * FROM table1";
$resultSet = odbc_exec($sqllink, $sql);

// Create an array and store the results

$queryResult = array();

while ($data = odbc_fetch_array($resultSet)) {

    // push the required content into the array

    $queryResult[$data['id']]['name'] = $data[name];
}

foreach($queryResult as $row) {
    $sql = "SELECT * FROM table2";
    $resultSet2 = odbc_exec($sqllink, $sql);
    while ($data2 = odbc_fetch_array($resultSet2)) {

        // something here

    }
}

Collectives™ on Stack Overflow

MS SQL Query failed in PHP but not in MS SQL Server Management Studio

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related