Page 1 of 1
Warning:mssql_query(): Query failed in file.php
Posted: Tue Nov 17, 2009 7:07 pm
by ewphp-guru
I have a program where the front-end tool is programmed in PHP and the back-end tool is completed in sql server 2000. I'm getting the following error when running a command in php. It's running a sql call for a stored procedure and passing in parameters.
Warning: mssql_query()[function.mssql-query]: Query failed in file.php.
I was able to run this stored procedure through query analyzer with all the correct parameters. It ran with no errors. When I run the same query through the php code, thats when I get the error stated above.
I've tried to debug the stored procedure, but can't find any issue.
Any assistance on how I can debug this error or fix it, would be appreciated.
This issue just started to occur a month ago.
Re: Warning:mssql_query(): Query failed in file.php
Posted: Tue Nov 17, 2009 7:33 pm
by Weiry
Well without actually seeing any of the code its quite difficult to see what your problem is.
Have you tried printing out your SQL statement from inside your PHP code?
This could give a clear indication of where the problem may be.
But again, without code, its impossible to identify the problem.
Re: Warning:mssql_query(): Query failed in file.php
Posted: Wed Nov 18, 2009 1:28 am
by angelicodin
I would also check the DB connection variables and double check you are connecting ;p
Re: Warning:mssql_query(): Query failed in file.php
Posted: Wed Nov 18, 2009 10:57 am
by ewphp-guru
I'm attaching the php code to this issue. The part where I'm seeing the error is where the php code calls the following stored procedure:
Code: Select all
$sql = "
EXEC ADD_RECEIVERS_ADJUSTMENTS
@id = ".$_REQUEST['ID']."
, @amount = ".$_REQUEST['EXTENDED_VALUE']."
, @account_2 = ".$_REQUEST['cmbAccount_2']."
, @acct_unit_2 = '".$_REQUEST['cmbAcct_unit_2']."'
, @transaction_date = '".$_REQUEST['transaction_date']."'
, @comment = '".$comment."'
";
<?php
require_once('HEADER.php');
?>
<html>
<head>
<title>View Receiver Detail</title>
<link rel="stylesheet" href="hf.css" type="text/css">
<link rel="STYLESHEET" type="text/css" href="calendar/calendar.css">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" src="calendar/simplecalendar.js" type="text/javascript"></script>
<script language="JavaScript">
//function init(){
//}
//function destroy(){
//}
</script>
</head>
<body>
<h1 align="center"> Receiver Detail</h1>
<p align="center" class="date"><?php if($_COOKIE['user']){ echo $_COOKIE['user']; } else { echo "??";};?> - <? echo date('M d, Y, h:i:s A'); ?></p>
<?php
// DEBUG /////////////////////////////////////////////////////////////////////////
//>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
//print out array keys and values
//foreach (array_keys($_REQUEST) as $items){
// echo "$items = \"$_REQUEST[$items]\""."<br>";
//}
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
?>
<?php
// PROCESS FORM REQUESTS //////////////////////////////////////////////////////////
// Update data if requested
$notes = '';
if($_REQUEST['x'] == 'HD')
{
// Set record On-Hold
if ($_REQUEST['onHold'] && $_REQUEST['ID']) {
// Create and run SQL statement
$sql = "
UPDATE vwRECEIVERS_FREIGHT
SET MATCH_FL = 'H'
WHERE ID = ".$_REQUEST['ID']."
";
//echo $sql."<br>"; //>>>>><<<<
mssql_query($sql) or die('Query failed' );
// Update @notes
$notes .= "Record placed On-Hold<br>\n";
} else {
// Update @notes
$notes .= "No changes made - Checkbox must be checked for changes to take place<br>\n";
}
} elseif($_REQUEST['x'] == 'AJ') {
//Make sure we are not trying to create an adjustment in a closed period
$sql = "
SELECT
REC_TO_DATE AS LAST_PERIOD_END_DATE
,CASE WHEN REC_TO_DATE >= '".$_REQUEST['transaction_date']."' THEN 'Y' END AS PERIOD_CLOSED_FL
FROM (
SELECT TOP 1 REC_TO_DATE
FROM fnPERIOD_DATES('')
WHERE PAY_TO_DATE = (SELECT MAX(PERIOD_END_DATE) FROM FREIGHT_CLOSE)
) AS XX
"; //>>>can we simplify this by creating a function/procedure/view?<<<
// Run query and print results in HTML
$result = mssql_query($sql) or die('Query failed');
$row = mssql_fetch_array($result, MSSQL_ASSOC);
if($row['PERIOD_CLOSED_FL'] != 'Y'){
// Make sure COMMENTS is formated so that it doesn't cause SQL problems
$comment = str_replace("\'", "''", $_REQUEST['comment']);
//period is not closed so create adjustment
$sql = "
EXEC ADD_RECEIVERS_ADJUSTMENTS
@id = ".$_REQUEST['ID']."
, @amount = ".$_REQUEST['EXTENDED_VALUE']."
, @account_2 = ".$_REQUEST['cmbAccount_2']."
, @acct_unit_2 = '".$_REQUEST['cmbAcct_unit_2']."'
, @transaction_date = '".$_REQUEST['transaction_date']."'
, @comment = '".$comment."'
";
//echo $sql."<br>"; //>>>>><<<<
mssql_query($sql) or die('Query failed');
$notes .= "Adjustment created<br>\n";
} else {
//Period is closed so don't do anything
$notes .= "Cannot create an adjustment in a closed period (last REC closed period end date = ".$row['LAST_PERIOD_END_DATE'].")<br>\n";
}
} elseif($_REQUEST['x'] == 'PR') {
/*
// Remove On-Hold
if ($_REQUEST['onHold'] && $_REQUEST['ID']) {
// Create and run SQL statement
$sql = "
UPDATE vwRECEIVERS_FREIGHT
SET MATCH_FL = 'P'
WHERE ID = ".$_REQUEST['ID']."
";
//echo $sql."<br>"; //>>>>><<<<
mssql_query($sql) or die('Query failed' );
// Update @notes
$notes .= "Record released On-Hold<br>\n";
} else {
// Update @notes
$notes .= "No changes made - Checkbox must be checked for changes to take place<br>\n";
}
*/
}
?>
<table width="90%" border="0" cellspacing="1" cellpadding="5" align="center">
<tr>
<td width="20%">
<input type="button" name="Submit22" value="Close" onClick="if(opener) opener.focus(); self.close();">
</td>
<td align="center" class="notes">
<?php echo $notes; ?>
</td>
<td width="20%">
<div align="right"> </div>
</td>
</tr>
</table>
<table width="50%" border="0" cellspacing="1" cellpadding="5" align="center">
<tr>
<td>
<?php
// Build SQL query
$sql = "
SELECT *
FROM vwRECEIVERS_EXTENDED
WHERE ID = ".$_REQUEST['ID']."
";
// Run query and print results in HTML
$result = mssql_query($sql) or die('Query failed');
$row = mssql_fetch_array($result, MSSQL_ASSOC);
?>
<table border="0" cellspacing="1" cellpadding="5" align="center">
<? foreach (array_keys($row) as $key){ ?>
<tr>
<td class="column_titles" ><? echo $key; ?> </td>
<td width="80%" bgcolor="#FFFFFF">
<? echo $row[$key]; ?>
</td>
</tr>
<? } ?>
</table>
</td>
</tr>
</table>
<p align="center" class="notes">
<?php echo $notes; ?>
<img src="images/blank.gif" width="13" height="13"></p>
<div id="ie5menu" class="skin0" onMouseover="highlightie5(event)" onMouseout="lowlightie5(event)" onClick="jumptoie5(event)" display:none>
<? if(!preg_match('/HOLD/',$row['STATUS']) && !preg_match('/^Y/',$row['PERIOD_CLOSED_FL'])){ ?>
<div class="menuitems" onclick="form1.viewMode.value='EDIT_HOLD'; form1.submit();">Place Record on-Hold</div>
<? } ?>
<div class="menuitems" onclick="form1.viewMode.value='EDIT_ADJ'; form1.submit();">Create an Adjustment</div>
<hr>
<div class="menuitems" onclick="if(opener) opener.focus(); self.close();">Close</div>
</div>
<script language="JavaScript1.2" src="javascript/context_menu.js" type="text/javascript"></script>
<form name="form1" method="post" action="<? echo basename($HTTP_SERVER_VARS['PHP_SELF']); ?>#actions">
<a name="actions"></a>
<input type="hidden" name="x" value="">
<input type="hidden" name="viewMode" value="<?php echo $_REQUEST['viewMode']; ?>">
<input type="hidden" name="ID" value="<? echo $_REQUEST['ID'];?>">
<!-- ///// VIEW /////////////////////////////////////////////////////////////// -->
<? if(!preg_match('/^EDIT_/', $_REQUEST['viewMode'])) { ?>
<? if(!preg_match('/HOLD/',$row['STATUS']) && !preg_match('/^Y/',$row['PERIOD_CLOSED_FL'])){ ?>
<a href="javascript: form1.viewMode.value='EDIT_HOLD'; form1.submit();" title="place record on-hold">
<img src="images/hold.gif" width="16" height="16" border="0">
</a>
<? } ?>
<a href="javascript: form1.viewMode.value='EDIT_ADJ'; form1.submit();" title="create an adjustment">
<img src="images/adjust.gif" width="13" height="13" border="0">
</a>
<!-- ///// EDIT /////////////////////////////////////////////////////////////// -->
<? } elseif(strtoupper($_REQUEST['viewMode']) == 'EDIT_HOLD') { ?>
Place record on_hold
<input type="checkbox" name="onHold" value="checkbox">    
<a href="javascript: form1.viewMode.value='VIEW'; form1.x.value='HD'; form1.submit();" title="place record on-hold">
<img src="images/save.gif" width="15" height="13" border="0">
</a>
<a href="javascript: form1.viewMode.value='VIEW'; form1.submit();" title="do NOT make changes and return to view mode">
<img src="images/x.gif" width="15" height="14" border="0">
</a>
<? } elseif(strtoupper($_REQUEST['viewMode']) == 'EDIT_ADJ') { ?>
<table border="0" cellspacing="1" cellpadding="5" >
<td colspan=6>
<b>Adjustment:</b>
<a href="javascript: form1.viewMode.value='VIEW'; form1.x.value='AJ'; form1.submit();" title="create adjustments">
<img src="images/save.gif" width="15" height="13" border="0">
</a>
<a href="javascript: form1.viewMode.value='VIEW'; form1.submit();" title="do NOT make changes and return to view mode">
<img src="images/x.gif" width="15" height="14" border="0">
</a>
</td>
<tr>
<td align="center" valign="bottom">Amount to adjust <br>record by:<br>
<input type="text" name="EXTENDED_VALUE" value="<? echo $row['EXTENDED_VALUE']*(-1); ?>" size="10">
</td>
<td align="center" valign="bottom">Account to apply <br>adjustment to:<br>
<select name="cmbAccount_2" onChange="">
<!--option value="NULL" selected>-none-</option-->
<?php
// Build SQL query
$sql = '
SELECT *
FROM ACCOUNT_DETAIL
ORDER BY ACCOUNT
';
$result1 = mssql_query($sql) or die('Query failed');
$row1='';
while($row1 = mssql_fetch_array($result1)) {
echo "<option value=\"$row1[ACCOUNT]\"";
if(trim($row['ACCOUNT']) == trim($row1['ACCOUNT'])) echo "selected";
echo ">$row1[ACCOUNT] ($row1[DESCRIPTION])</option>\n";
}
?>
</select>
</td>
<td align="center" valign="bottom">Account Unit to <br>apply adjustment to:<br>
<select name="cmbAcct_unit_2" onChange="">
<!--option value="NULL" selected>-none-</option-->
<?php
// Build SQL query
$sql = '
SELECT *
FROM ACCOUNT_UNIT_DETAIL
ORDER BY ORG_CODE
';
$result1 = mssql_query($sql) or die('Query failed');
$row1='';
while($row1 = mssql_fetch_array($result1)) {
echo "<option value=\"$row1[ACCT_UNIT]\"";
if(trim($row['ACCT_UNIT']) == trim($row1['ACCT_UNIT'])) echo "selected";
echo ">$row1[ACCT_UNIT] ($row1[ORG_CODE])</option>\n";
}
?>
</select>
</td>
<td align="center" valign="bottom">
Transaction <br>Date:<br>
<input type="text" name="transaction_date" size="11" onfocus="form1.transaction_date.blur()" value="<? echo date('m/d/Y'); ?>">
<a href="javascript: void(0);" onmouseover="if (timeoutId) clearTimeout(timeoutId);window.status='Show Calendar';return true;" onmouseout="if (timeoutDelay) calendarTimeout();window.status='';" onclick="g_Calendar.show(event,'form1.transaction_date', false, 'mm/dd/yyyy'); return false;"><img src="calendar/calendar.gif" name="imgCalendar" width="34" height="21" border="0" alt=""></a>
</td>
<td align="center" valign="bottom">
Comments:<br>
<textarea name="comment" cols="20"></textarea>
</td>
</tr>
</table>
<? } ?>
<!-- ///// OTHER /////////////////////////////////////////////////////////////// -->
</form>
<hr>
<br><br><br><br><br><br>
</body>
</html>
<?php
require_once('FOOTER.php');
?>
Re: Warning:mssql_query(): Query failed in file.php
Posted: Wed Nov 18, 2009 7:48 pm
by angelicodin
Could you encase this code in tags so we can see the syntax highlighting.

Re: Warning:mssql_query(): Query failed in file.php
Posted: Thu Nov 19, 2009 12:12 am
by Weiry
Definitely agreeing with angelicodin
See the link in my comment, Specifically "In a Nutshell: #9"