[PHP-DB] Mssql_query() failure with large queries?

Hello,

I'm new to mssql and php. I've taken over some php code that makes a query to a mssql DB. If the data returned is small enough, it works fine, but if it's too big, I get the following error:

Warning: mssql_query(): Query failed in c:\program files\apache group\apache\htdocs\monitoring\availability_corda. php on line 731

Fatal error: Call to undefined function: mssql_error() in c:\program files\apache group\apache\htdocs\monitoring\availability_corda. php on line 732

The problem is that the amount of data it's returning is not really that much -- maybe 5 columns by 100 rows. I think the problem may be the complicated computation that is being done on the DB side to return the data. I know some temp tables are being built on the fly and store procedures are being called.

Now, I've tried increasing the mssql timeout and the php memory limit.

Does anyone have any insight in to this problem? Any help is greatly appreciated.

Thanks!

Karim Varela
i-booze.com

 

mssql_query(): Query failed in ... It can't run sql command --> re check your sql before use mssql_query()

 

Thanks mind for your reply. The SQL commands work fine in MS SQL Server Management Studio and smaller queries work find from the PHP code. It's just when I make larger queries that it failes.

Any other advice?

 

Can you post your actual code?

 

Can you post your php.ini file about mssql ?

 

Thanks guys. I'd be happy to. Here's the php code:
Query setup & execution:

$historicalGraphTimerQuery = "EXEC dbo.spGetCumulativeTimerAvailability_32 '".$reportDateStart."',
'".$reportDateEnd."',
".$currentGraphTimerId.",
".$currentGraphMonitorID;


if (isset($_GET['debug'])){print "<font color=red>Line 619: </font>$historicalGraphTimerQuery<p>";}

$historicalGraphTimerQueryResult = mssql_query($historicalGraphTimerQuery)
or die("SELECT Error: ".mssql_error());



Stored procedure dbo.spGetCumulativeTimerAvailability_32:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROC [dbo].[spGetCumulativeTimerAvailability_32]
@start_time datetime,
@end_time datetime,
@timer_id int,
@monitor_id int
AS
DECLARE @curr_start_time datetime
DECLARE @total_runs int
DECLARE @total_pass int
DECLARE @total_fail int
DECLARE @uptime decimal(10,2)

DECLARE @cumulative_timer_availability table
(sample_time datetime,
runs int,
pass int,
fail int,
uptime decimal(10,2))



DECLARE cursor_start_time cursor
FOR
select distinct start_time
from vw_mobile_complete_results_accessibility_32
where start_time between @start_time and @end_time
and script_timer_id = @timer_id
and monitor_id = @monitor_id
order by start_time asc


OPEN cursor_start_time
FETCH NEXT FROM cursor_start_time INTO @curr_start_time
WHILE @@FETCH_STATUS = 0
BEGIN



set @total_runs = (select count(distinct run_id) as 'total_runs'
from vw_mobile_complete_results_accessibility_32
where start_time between @start_time and @curr_start_time
and script_timer_id = @timer_id
and monitor_id = @monitor_id)

-- Modified May 16, 2006 by Timothy A Hutchison ------------------------------
-- Any timer with none zero result for in the latency field is a pass! -------
--------------------------------------------------------------------------------

-- set @total_pass = (select count(distinct run_id) as 'total_pass'
-- from vw_pm_mobile_complete_results
-- where start_time between @start_time and @curr_start_time
-- and timer_id = @timer_id
-- and result = 'Success')
--
--------------------------------------------------------------------------------

set @total_pass = (select count(distinct run_id) as 'total_pass'
from vw_mobile_complete_results_accessibility_32
where start_time between @start_time and @curr_start_time
and script_timer_id = @timer_id
and monitor_id = @monitor_id
and latency <> 0)

-- Modified May 16, 2006 by Timothy A Hutchison ------------------------------
-- Any timer with zero result for in the latency field is a failure! -------
--------------------------------------------------------------------------------


-- set @total_fail = (select count(distinct run_id) as 'total_fail'
-- from vw_pm_mobile_complete_results
-- where start_time between @start_time and @curr_start_time
-- and timer_id = @timer_id
-- and result = 'Failure')
--
-----------------------------------------------------------------------------------

set @total_fail = (select count(distinct run_id) as 'total_fail'
from vw_mobile_complete_results_accessibility_32
where start_time between @start_time and @curr_start_time
and script_timer_id = @timer_id
and monitor_id = @monitor_id
and latency = 0)



set @uptime = ((convert(decimal(10,2),(@total_runs - @total_fail))/@total_runs)*100)


insert into @cumulative_timer_availability
select @curr_start_time AS 'sample_time',
@total_runs AS 'runs',
@total_pass AS 'pass',
@total_fail AS 'total_fail',
@uptime AS 'uptime'

FETCH NEXT FROM cursor_start_time INTO @curr_start_time




END

CLOSE cursor_start_time
DEALLOCATE cursor_start_time


select * from @cumulative_timer_availability



php.ini file:



[MSSQL]; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links. -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent). -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Connect timeout
;mssql.connect_timeout = 120

; Query timeout
;mssql.timeout = 600

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096

; Limits the number of records in each batch. 0 = all records in one batch.
;mssql.batchsize = 0

; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
;mssql.datetimeconvert = On

; Use NT authentication when connecting to the server
mssql.secure_connection = Off

; Specify max number of processes. Default = 25
;mssql.max_procs = 25

[Assertion]
; Assert(expr); active by default.
;assert.active = On

; Issue a PHP warning for each failed assertion.
;assert.warning = On

; Don't bail out by default.
;assert.bail = Off

; User-function to be called if an assertion fails.
;assert.callback = 0

; Eval the expression with current error_reporting(). Set to true if you want
; error_reporting(0) around the eval().
;assert.quiet_eval = 0