Nested stored procedure silent timeout?

Question

Hi,

Is there any form of implicit timeout for the execution of a stored procedure via the SQL PHP driver?

I am calling to a stored procedure that updates values that already exist and inserts new ones. For the insert part, there is a nested call to another stored procedure within the first which performs the actual insertion of a data point given a value and some metadata. I am finding that often the script reports success (and there is no sqlsrv_errors() output aside from '1' for success), but checking with the database, only a subset of values have been inserted. If I then run the same stored procedure in management studio on the same machine, all the values are picked up and inserted successfully.

If it helps, I am updating values in the first instance via a single UPDATE statement across a join. The insert part is then a loop through all the rows that are new which are then passed to this nested stored procedure.

I have tried this call using both the raw EXEC syntax and the recommended { CALL xxx } syntax but I see the same problems.

Lastly I am not seeing this same problem via any of my other inline queries (i.e. those not using stored procedures).

I am using SQL Server 2005 and PHP 5.3 with the 1.1 non-thread-safe dll compiled for VC6. I have the latest SQL 2008 native client installed from the April update pack.

Many thanks for any potential help.

Thanks,
-Will

Answer 1

By default, the driver will wait indefinitely. Please check the description of “QueryTimeout” option at:

http://msdn.microsoft.com/en-us/library/cc296181(SQL.90).aspx

Moreover, if the statement execution times out, then the return value indicates failure and not success.

Please post the query and the code of the stored proc, and we’ll try to investigate further.

 

Thank you,

Serban

---

Serban Iliescu - MSFT

Answer 2

Many thanks for the link, this is what I suspected.

Here is the part of my stored procedure that is causing problems. Prior to this it is updating values that already exist in an external table by updating across a join to the unique ID for that table. That is all working fine and the query is an inline UPDATE ... SET ... FROM ... WHERE ...

The part below is then a loop which goes through imported data that doesn't already exist (kpi_data_id is NULL) and therefore INSERTs it:

-- Now create any new data from the import
WHILE EXISTS (SELECT * FROM #import WHERE kpi_data_id IS NULL AND processed = 0)
BEGIN
	/* Code here sets the values of the arguments below, i.e. @org_id, @kpi_id, @cval_id, @date, @date_type, @country_id, @value, Source_id, @confidence_id, @currency_id, @dimval_id, @dimval_id_4
	
	I have checked all these are being returned correctly */
	
	IF @value <> 0 AND @value IS NOT NULL
	BEGIN
		EXEC sp_Kpi_Insert_Data 'wi-import', @kpi_id, @country_id, 3, 'N', @confidence_id, '5', @source_id, @date_type, @date, @currency_id, 0, @value, @approval_hash, 0, @dimval_id, '', '', '', '', '3', @dimval_id_4, '1', @org_id, @inserted_id = @kpi_data_id OUTPUT
		UPDATE #import SET kpi_data_id = @kpi_data_id WHERE org_id = @org_id AND kpi_id = @kpi_id AND cval_id = @cval_id AND date = @date AND date_type = @date_type
	END
	
	UPDATE #import SET processed = 1 WHERE org_id = @org_id AND kpi_id = @kpi_id AND cval_id = @cval_id AND date = @date AND date_type = @date_type
END

The call that is often failing is the nested stored procedure sp_Kpi_Insert_Data which creates the data point across 4 different tables and returns its unique ID (into the output @kpi_data_id) from the primary table.

It is as if it is creating a few data points, but then arbitrarily stops, e.g. for a sample I have run just now, 24 data points were required and only 5 created. It does not seem to be a problem with any particular data either, as running a 'problematic' data point that was previously not created on its own is fine - the data point gets INSERTed successfully. Additionally, I then ran the stored procedure from management studio and the remaining 19 were then happily created without error.

If sp_Kpi_Insert_Data fails to run, it should report failure to my outer stored procedure and hence the PHP driver, correct?

Many thanks,

-Will

Answer 3

The PHP driver does not interpret the query string in any way before passing it to the ODBC layer (SQL Server 2008 Native Client). The errors are propagated from the SQL server to the ODBC driver and from there to the PHP driver.

I assume that your PHP code checks the return value of the APIs, and if the return is false, then it checks the errors reported through sqlsrv_errors(). However, it will help us understand better the context if you are willing to post the PHP code as well.

 

Thank you,

Serban

---

Serban Iliescu - MSFT

Answer 4

Hi Serban,

Apologies for the extended silence. Yes, I believe I am checking the return values of the APIs correctly, but during testing I have also been displaying the contents of sqlsrv_errors() regardless just in case. The return value for the sqlsrv_query() calls are along the lines of e.g. "Resource Statement #22", they aren't returning false.

Here is the coding calling the stored procedure (my previous post is a cut from MergeImportDataWithKpiData, sp_Kpi_Insert_Data is the inner stored procedure that isn't executing for all rows):

if (count($approve) > 0)
{
	// Approve the data in the data store
	$hash 	= $this->authentication->user['login'].'-'.date('Y-m-d-H-i-s');
	$query	= "UPDATE gsma.import_data_store SET approved = 1, approval_hash = ?, last_update_by = ?, last_update_on = getdate() WHERE id IN (".implode(',', $approve).")";
	$this->database->query($query, array($hash, $this->authentication->user['login']));
	
	// Merge the approvals with the Master kpi_data table for calculation
	$query	= "{ CALL MergeImportDataWithKpiData (?) }";
	$result = $this->database->query($query, array($hash));
	
	$this->femto->_notify(5, 'Approved <strong>'.$this->language->pluralize_if(count($approve), 'data point').'</strong> in the data store');
}

There is an objected-orientated wrapper $this->database->query around sqlsrv_query(), this is as follows:

function query($query, $params = null, $return = false)
{
	switch($return)
	{
		case 'last_affected_id':
			$query .= "\n"."SELECT scope_identity() AS id";
			break;
	}
	
	if (is_null($params))
	{
		$this->queries[] = $query;
		if (!$result = sqlsrv_query($this->connection, $query))
		{
			$this->femto->_notify(2, 'SQL Server error: '.print_r(sqlsrv_errors()), 'error');
			$result = false;
		}
	}
	else
	{
		$this->queries[] = $query;	// TODO: replace $params in $query
		if (!$result = sqlsrv_query($this->connection, $query, $params))
		{
			$this->femto->_notify(2, 'SQL Server error: '.print_r(sqlsrv_errors()), 'error');
			$result = false;
		}
	}
	
	switch($return)
	{
		case 'last_affected_id':
			return $this->last_affected_id($result);
			break;
		case 'rows_affected':
			return $this->rows_affected($result);
			break;
		default:
			return $result;
			break;
	}
}

Many thanks for your continued perseverance on this, I'd be grateful if you'd let me know if I'm doing anything stupid here.

Regards,

-Will

Answer 5

Hi Will,

I looked at your code and I cannot claim that I saw anything wrong. The only recommendation I have (and I don’t know if it applies to your code) is to check whether the PHP objects such as connection and statement remain in scope.  For instance, the following code will fail because the connection object is going out of scope (and it will be automatically closed):

 

function  ExecQuery($server, $table)

{

$conn = sqlsrv_connect($server);

return (sqlsrv_query( $conn, “SELECT * FROM $table”));

}

 

$stmt = ExecQuery($server, $table);

 

Thanks,

Serban

 

 

---

Serban Iliescu - MSFT