Connecting to 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Connecting to 2005

Author

Topic

kingsgambit
Starting Member

4 Posts

Posted - 2008-12-02 : 07:25:32

I have a script that runs ok in SQL 2000 but returns all records if I run it in SQL 2005, I have had a look on the web and it mentioned the way I connect to the sql 2005 can anybody point me in the right direction for this.

Script below

<?php

$account=strtoupper($account);

$server="pbtest1,1433";
$username="xxx";
$password="xxxxxxx";

$sqlconnect=mssql_connect($server,$username,$password);

if (!$sqlconnect)
{
printf("Cannot connect to server\n");
exit;
}

$sqldb=mssql_select_db("ELLIOTTHIRE",$sqlconnect);

$sql="select top 1
name,
left(natacct,1) natacct,
onstop
from lookup
where acct = '$account'";

$results=mssql_query($sql);

$nat=' [NATIONAL ACCOUNT]';

while ($row=mssql_fetch_array($results)) {

$name=strtoupper($row['name']);
$acct=strtoupper($acct);
$natacct=$row['natacct'];
$onstop=$row['onstop'];

if ($natacct==Y) {
echo "<caption align=center>".$name.": ".$account."".$nat."</caption>";
} else {
echo "<caption align=center>".$row['name'].": ".$account."</caption>";
}

}

if ($onstop==1) {

?>
<SCRIPT Language="Javascript">alert("***** This account is currently ON STOP! *****");</script>
<?php

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-02 : 14:10:09

What do you mean by returning all rows? Can you capture the text of the query being sent to the SQL Server, i.e.,


echo htmlspecialchars($sql);

...and try to run that in a Management Studio window?

___________________________
Geek At Large

kingsgambit
Starting Member

4 Posts

Posted - 2008-12-03 : 07:32:30

If I run the query

$sql="select top 1
name,
left(natacct,1) natacct,
onstop
from lookup
where acct = '$account'";

on the sql 2000 with the user inputting an account ($account)name
it returns the correct account.

Same script on sql 2005 it returns all accounts and not just the users inputted account

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 09:08:25

Understood... I was suggesting removing the script from the equation altogether, to see if there is some reason the SQL Server itself is behaving differently.

Instead of running the query stored in the $sql variable, try printing it to the screen, copying it into a Management Studio window and see if you generate the same result set. If you don't, then something is wonky with the connection. If you do, the connection is not the place to concentrate your efforts.

The query you have issued should never return more than one row, under any circumstances. I would also look at the


while ($row=mssql_fetch_array($results))

line. You are looping something that is not supposed to have more than one result set. Perhaps instead you could write:


if (mssql_num_rows($results) == 1) {
	$row=mssql_fetch_array($results);
	$name=strtoupper($row['name']);
	$acct=strtoupper($acct);
	$natacct=$row['natacct'];
	$onstop=$row['onstop'];
}

also, the


if ($natacct==Y)

isn't right, in this case you are treating "Y" like a constant, which I do not think is your intention. I think you're looking for:


if ($natacct == 'Y')

Please let me know if I've completely misread your problem.

___________________________
Geek At Large

kingsgambit
Starting Member

4 Posts

Posted - 2008-12-03 : 11:10:00

I think I know what the problem is but not sure how to fix it, the html page that the user enters the account name into is not passing to the php page, so the result is just returning all accounts.
Is there a difference in sql 2005 for passing variables from sql 2000 as the same html page in sql 2000 passes the variables with no problem

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 12:04:14

Well, see, the thing is, the script you have is not passing any variables at all... at least, not to SQL Server. By the time you're sending stuff to SQL Server, it's just a query string, which should respond identically in 2005 as it does in 2000. It sounds like there might be a problem with your forms handling or your script; I could help you out with that if you'd like, but what you are describing does not seem to have any relation to SQL Server at all.

___________________________
Geek At Large

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources