Query no longer workin after move SQL2005

[adaptabl]

We have web application that uses data from a SQL database. The old server was MS SQL2000 and was upgraded to SQL2005. The querry:

$summary_query = "select cast(request.REQUESTID as int(4)) as RequestId, cast((select CustFieldValue from azteca.ReqCustField as CustomFields where CustomFields.REQUESTID=request.REQUESTID and CustFieldID=2) as varchar(11)) as CallerFirstName, cast((select CustFieldValue from azteca.ReqCustField as CustomFields where CustomFields.REQUESTID=request.REQUESTID and CustFieldID=3) as varchar(11)) as CallerLastName, Request.PROBADDRESS as ProblemAddress, CONVERT(CHAR(11),DATETIMEINIT,111) as DateInitiated from azteca.REQUEST as Request WHERE 1=1 ";

worked on the old server but no longer gives any results on the new server. Any ideas on what is no longer compatable? This is an application I took over from a former employee. The table opens fine and I can querry from the query analyzer correctly. I can also access fields directly in other parts of the code. I just seems to this querry that is failing.

[PHP_Chimp]

What errors is your code giving you?

If you are not getting any errors then what code are you using to connect to the database?
As (usually) database errors are quite descriptive so you can use them to sort your problem.

Welcome along.

[mrscruff]

Just a thought, not some security setting where you can only send one query at a time from web server, so the embedded 'select' would cause a problem.

This would mean that the query would still work in your 'query analyzer', and you could access tables using a single 'select' statement.

[adaptabl]

This the code:

<?php
// check that we already have an intranet session started. always do this first for authenticated sessions!
session_start();
//if(!isset($_SESSION['intra_user'])){
//header("Location: login.php?action=");
//}

// include scripts go here. be sure to document the function of your include.
// cityworks.php creates an ADODB connection object, $conn
require_once "Connections/cityworks.php";
// end of include section

// establish required session variables if not already set.
if (!isset($_SESSION['DateFilter'])) {
if (!isset($_REQUEST['DateFilter'])) $_SESSION['DateFilter'] = date("Y");
else $_SESSION['DateFilter'] = $_REQUEST['DateFilter'];
} else if (isset($_REQUEST['DateFilter']) && $_SESSION['DateFilter'] != $_REQUEST['DateFilter']) $_SESSION['DateFilter'] = $_REQUEST['DateFilter'];

if (!isset($_SESSION['GridOrder'])) $_SESSION['GridOrder'] = "RequestId";
if(isset($_REQUEST['GridOrder']) && $_REQUEST['GridOrder'] != $_SESSION['GridOrder']) $_SESSION['GridOrder'] = $_REQUEST['GridOrder'];

if (!isset($_SESSION['Status'])) $_SESSION['Status'] = "ALL";
if(isset($_REQUEST['Status']) && $_REQUEST['Status'] != $_SESSION['Status']) $_SESSION['Status'] = $_REQUEST['Status'];

// define queries to be used here. this will keep the code clean for now. we'll work on customization later. and please, document the fields the query returns.
// summary table. filtered by year, custom sorted enabled.
// fields: RequestId, CallerFirstName, CallerLastName, ProblemCode, DateInitiated
$summary_query = "select cast(request.REQUESTID as int(4)) as RequestId, cast((select CustFieldValue from azteca.ReqCustField as CustomFields where CustomFields.REQUESTID=request.REQUESTID and CustFieldID=2) as varchar(11)) as CallerFirstName, cast((select CustFieldValue from azteca.ReqCustField as CustomFields where CustomFields.REQUESTID=request.REQUESTID and CustFieldID=3) as varchar(11)) as CallerLastName, Request.PROBLEMCODE as ProblemCode, CONVERT(CHAR(11),DATETIMEINIT,111) as DateInitiated from azteca.REQUEST as Request WHERE 1=1 ";
if($_SESSION["DateFilter"]!="ALL") $summary_query = sprintf($summary_query . "AND DATEPART(yyyy,DATETIMEINIT) = %s ",$_SESSION['DateFilter']);
if($_SESSION["Status"]!="ALL") $summary_query = sprintf($summary_query . "AND Status = '%s' ",$_SESSION["Status"]);
$summary_query = sprintf($summary_query . "ORDER BY '%s'",$_SESSION["GridOrder"]);

// year picker. returns a set of years for which service requests exist. used for filtering summary_query.
// fields: Year
$yearpicker_query = sprintf("SELECT DISTINCT DATEPART(yyyy,DATETIMEINIT) as Year from azteca.REQUEST");

// same as year picker but for status field.
$status_query = sprintf("SELECT DISTINCT STATUS as Status from azteca.REQUEST");
// end of queries section.

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Town of Tecumseh</title>
<script language="javascript">

</script>
<style>
tr {
}
tr.row0 {
background-color: #99FFFF;
}
tr.row1 {
background-color: #FFFFFF;
}
tr.row0:hover {
background-color: #3399FF;
cursor: pointer;
}
tr.row1:hover {
background-color: #3399FF;
cursor: pointer;
}
th,td {
padding-left: 2.5px;
padding-right: 2.5px;
border-bottom: 1px solid rgb(180,180,180);
}
th.sort:hover {
cursor: pointer;
}
thead {
border-bottom: 1px solid rgb(0,0,0);
font-weight: bold;
}
select,input {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size: 9px;
}
table {
width: 375px;
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
border-spacing: 0px;
border-collapse: collapse;
}

</style>
</head>

<body>
<?php
// this places a dropdown list of years to choose from. only years with service request records are displayed. this helps to keep the size of our summary table down. the page is automatically refreshed when a year is selected. sort order and other applied filters are maintained. this also places a form in the html which contains the hidden fields for our sort order code. other sorting/filtering fields may be defined here.
echo '<form id="picker" method="POST" action="intranet_cityworkscomplaints.php">';
echo '<select name="DateFilter" onChange="javascript:document.forms[\'picker\'].submit();">';
echo '<option value="ALL" ';
if($_SESSION["DateFilter"]=="ALL") echo 'SELECTED ';
echo '>ALL</option>';
$picker_result = $conn->execute($yearpicker_query);
while (!$picker_result->EOF){
echo '<option value="'.$picker_result->Fields['Year']->Value.'" ';
if ($picker_result->Fields['Year']->Value==$_SESSION["DateFilter"]) echo 'SELECTED ';
echo '>'.$picker_result->Fields['Year']->Value.'</option>';
$picker_result->MoveNext();
}
echo '</select>';
// status picker.
echo '<select name="Status" onChange="javascript:document.forms[\'picker\'].submit();">';
echo '<option value="ALL" ';
if($_SESSION["Status"]=="ALL") echo 'SELECTED ';
echo '>ALL</option>';
$status_result = $conn->execute($status_query);
while (!$status_result->EOF){
echo '<option value="'.$status_result->Fields['Status']->Value.'" ';
if ($status_result->Fields['Status']->Value==$_SESSION["Status"]) echo 'SELECTED ';
echo '>'.$status_result->Fields['Status']->Value.'</option>';
$status_result->MoveNext();
}
echo '</select>';
// sorting order
echo '<input type="hidden" name="GridOrder" value="'.$_SESSION['GridOrder'].'" /></form>';
$picker_result = null;
?>

<table cellspacing="0">
<thead><tr>
<th align="left" class="sort" onClick="javascript:document.forms['picker'].GridOrder.value='RequestId';document.forms['picker'].submit();">ID</th>
<th align="left" class="sort" style="width:30%;" onClick="javascript:document.forms['picker'].GridOrder.value='CallerLastName';document.forms['picker'].submit();">Caller Name</th>
<th align="left" class="sort" onClick="javascript:document.forms['picker'].GridOrder.value='ProblemCode';document.forms['picker'].submit();">Problem Code</th>
<th align="right" class="sort" style="width:20%;" onClick="javascript:document.forms['picker'].GridOrder.value='DateInitiated';document.forms['picker'].submit();">Initiated On</th>
</tr></thead>
<tbody><form name="srdetail" action="intranet_cityworksservicerequest.php" method="post">
<?php
$summary_result = $conn->Execute($summary_query);
$i = 0;
while(!$summary_result->EOF){
$q = $i % 2;
echo '<tr class="row'.$q.'" onClick="javascript:document.forms[\'srdetail\'].RequestId.value=\''.$summary_result->Fields['RequestId']->Value.'\';document.forms[\'srdetail\'].submit();">
<td align="left" >'.$summary_result->Fields['RequestId']->Value.'</td>
<td align="left" >'.$summary_result->Fields['CallerFirstName']->Value.' '.$summary_result->Fields['CallerLastName']->Value.'&nbsp;</td>
<td align="left" >'.$summary_result->Fields['ProblemCode']->Value.'&nbsp;</td>
<td align="right" >'.$summary_result->Fields['DateInitiated']->Value.'</td>
</tr>';
$i++;
$summary_result->MoveNext(); //move on to the next record
}

$summary_result = null;
$conn = null;
?> <input type="hidden" name="RequestId" />
</form></tbody>
</table>
</body>
</html>

The connections file is(I removed the connection info):

<?php
/* Connect using ADODB */
$myServer = "*********";
$myUser = "********";
$myPass = "*******";
$myDB = "********";

//create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr); //Open the connection to the database
// $conn->SetFetchMode(ADODB_FETCH_ASSOC);
?>

I can do other queries against the database from SQL analyser(not this query). The drop downs work. No data for the body is available.

[adaptabl]

The interesting thing was that the application worked fine under SQL2000. The hosting site for the data virtualized the server and migrated to SQL2005. Now the application fails. They also switched to windows authentication. I had them set up an account with SQL authentication. That is the account I am now using.