March 25, 2011 at 7:27 am
Dear Colleagues,
App stack is:- Win 2008 server x64, SQLServer 2008 R2 Enterprise, php 5.3.5, php mssql driver 2 from Microsoft, Apache 2.2)
A db stored proc, when called from php, exits from the db prematurely after a few instructs. All error logs clear. Only afflicts the app when DML called more than a few times by the sp (SELECTs run OK). Proc OUTPUT returned to php is corrupt as well, as an additional effect. The proc runs clean when executed in Management Studio. Ugly workround to call sqlcmd from o/s shell to run the proc restores full execution. The problem has been replicated on another platform with different versions of the app stack. How have php and SQLServer got it so wrong between them? Advice much appreciated.
March 25, 2011 at 7:35 am
what is the proc doing? if the proc is accessing anything outside of the database, it might be stopping/erroring due to permissions..you mentiond something about sqlcmd to fix stuff;
maybe when you run the proc in SSMS as yourself , it works, but running the proc as the webuser doesn't have permissions.
can you show us what the proc was doing?
Lowell
March 25, 2011 at 7:49 am
Thanks for the response. To answer your questions.. .. ..
==>>what is the proc doing?
inserting a single row into a small table repeated
maybe 100 times. The proc quits silently after say 20
inserts. Tinkering shows any DML causes the premature return. Also, a futile "set " instruct in the sp hastens the quit.
==>>if the proc is accessing anything outside of the ==>>database...?
The proc and all resources are in the same server\instance\database
==>>it might be stopping/erroring due to permissions?
Same login principal used in all tests.
==>>..you mentiond something about sqlcmd to fix stuff?
Process control is:browser-- php-cgi -- php shell exec -- dos.bat -- sqlcmd -- stored proc. Always run as the same login principal as studio tests. /Dioscoredes
March 25, 2011 at 8:01 am
dioscoredes (3/25/2011)
==>>what is the proc doing?inserting a single row into a small table repeated
maybe 100 times. The proc quits silently after say 20
inserts. Tinkering shows any DML causes the premature return. Also, a futile "set " instruct in the sp hastens the quit.
ok, I guess the best thing would be to post the PROC code itself;must be something int he code that is failing, maybe it's generating a duplicate key and erroring; I'm guessing maybe a cursor instead of a set based operation to insert those hundred rows might be the issue, so I think seeing the actual code is essential
Lowell
March 25, 2011 at 8:24 am
Please find below test harness code to demmo the fault
Php demonstration
<?php
//
// -------------------------------------------------------------
// ---------connect to db ----------------------------
// -------------------------------------------------------------
//
/***************************** database procedure test harness *******************************************************
create table #temp_html (rowID integer identity, html varchar(max) );
drop procedure ReturnPAram
CREATE PROCEDURE ReturnParam
( @p_formID varchar(8)
,@p_partID varchar(3)
,@p_tblName varchar(32)
,@p_rtnData varchar(255) OUTPUT )
AS
declare @v_count INT = 1
,@file VARCHAR(255) = 'C:\WebAppLogs\HELPDB\trace.log'
,@v_text VARCHAR(255) = ' =>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx<='
,@v_test_text varchar(255);
begin
set @p_rtnData = 'Returnparam running .. ..';
while ( @v_count < 1000)
begin
set @v_test_text = convert(varchar(8),@v_count) + ' =>' + @v_text
insert into #temp_html
(html)
values
( @v_test_text );
-- EXEC writeTrace @file, @v_test_text
set @v_count = @v_count + 1;
end;
set @p_rtnData = 'Returnparam ends .. ..';
end;
declare @i_formID varchar(8) = 'QRF_00'
,@i_partID varchar(3) = '002'
,@i_tblName varchar(32)= 'HELP_Q_PSE0'
,@i_rtnData varchar(255)
exec ReturnParam
@i_formID
,@i_partID
,@i_tblName
,@i_rtnData OUTPUT
print '@i_rtnData=>' + @i_rtnData
select COUNT(*) from #temp_html
delete #temp_html
*************************************************************************************************************/
//
//
echo '<PRE> '."";
//
$connectionInfo = array( "Database" => $database, "UID" => $RWLogin, "PWD" => $RWpasswd);
//$connectionInfo = array( "Database" => $database, "UID" => 'sa', "PWD" => 'Aldebaran1' );
$conn = sqlsrv_connect( $SQLServer, $connectionInfo);
//
if( $conn === false )
{
echo '<B>temp::Web site could not connect to SQL Server
';
echo "<B>Please contact application support.
<";
die( print_r( sqlsrv_errors(), true));
}
//
// --- create temporary table for html code -------------------------------- create temp table
//
$tsql = "create table #temp_html (rowID integer identity, html varchar(max) ); ";
$stmt = sqlsrv_query( $conn, $tsql );
if( $stmt == false)
{
die( print_r( sqlsrv_errors(), true));
exit( 'error::create temp table ');
}
//
sqlsrv_free_stmt($stmt);
//
// -------------------------------------------------------------------------- populate table
//
$sql = '{ CALL ReturnParam(?, ?, ?, ?) }';
$inputVariable1 = 'QRF_00 ' ;
$inputVariable2 = '002';
$inputVariable3 = 'HELP_Q_PSE0';
//
$rtnData='=>xxxxxxxxxxxxxxxxxxxxxxxx<=';
$params = array(array($inputVariable1, SQLSRV_PARAM_IN),
array($inputVariable2, SQLSRV_PARAM_IN),
array($inputVariable3, SQLSRV_PARAM_IN),
array($rtnData, SQLSRV_PARAM_OUT,
SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),
SQLSRV_SQLTYPE_VARCHAR(50))
);
//
$stmt = sqlsrv_query($conn, $sql, $params);
if( $stmt == false)
{
die( print_r( sqlsrv_errors(), true));
exit( 'error::populate temp table ');
}
//
echo 'Return value: ' .$rtnData. "";
var_export($params);
//
//
sqlsrv_free_stmt($stmt);
//
// --------------------------------------------------------------------------- select from table
//
$tsql = "select html from #temp_html; ";
//
$stmt = sqlsrv_query( $conn, $tsql );
if( $stmt == false)
{
die( print_r( sqlsrv_errors(), true));
exit( 'error::query temp table ');
}
//
//
echo '<PRE> '."";
//
while ( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
$html = $row['html'];
echo $html. "";
}
//
//
sqlsrv_free_stmt($stmt);
//
sqlsrv_close($conn);
//
?>
/** from which we get 50 to 100 lines of output rather than the 999 expected. Hacking the code shows count(*) for the results table matches rows of printed output count so is SQLServer problem not php bug.
Also the rtnVal is corrupted **/[/font]
March 25, 2011 at 8:38 am
it looks like your procedure is recursively calling itself, correct? you might be hitting the max number of nesting levels or something;
I'm not sure what the proc is doing exactly yet, but that was my first impression as i read the details.
Lowell
March 25, 2011 at 8:45 am
Not sure we are scenting the right trail with this discussion.
Proc works when run in studio but not when called from php.
SQLServer2008 has 1000 transaction limit - test harness proc no where near this. This is a SQLServer internals problem - stack space exhausted or similar. Not my field.
March 25, 2011 at 11:08 am
Lowell is referring the maximum recursion level, not the max transaction level. Since your proc calls itself you need to be aware of it. Try this out in your test DB to see an example of hitting that limit:
SET NOCOUNT ON;
GO
CREATE PROC dbo.recurse_1
AS
BEGIN
EXEC dbo.recurse_1
END
GO
EXEC dbo.recurse_1
GO
DROP PROC dbo.recurse_1
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2011 at 11:18 am
I just noticed that your proc is not calling itself...I took that for granted initially based on previous posts.
That said...you're welcome for the quick demo on the recursion limit 🙂
Your proc looks fine...I think you'll have better luck asking for help in a PHP forum. Please post your findings though when you get to the bottom of it.
edit: there is a dedicated forum on MSDN for the SQL Server PHP Driver http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/threads
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2011 at 5:46 am
Many thanks for the recommended MSDN forum. Have posted the problem there. Will update this thread if anything useful come of it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply