Doing a query through php to sql server 2005 and get query timeout

[mikek12004]

doing a (not so simple) query -which works fine in sql server using PDO like this:

// attempt a connection
	try {
		$pdo = new PDO("mssql:host=$hostname,1433;dbname=$dbname;",$username,$password);
	} catch (PDOException $e) {
	die("ERROR: Could not connect: " . $e->getMessage());
	}
	// create and execute SELECT query
	$sql = "select datepart(week,(clroot.dochdsal.date1)) as weekno,datepart(year,(clroot.dochdsal.date1)) as yearno,clroot.litmsale.linkid,SUM(clroot.litmsale.openqnt) AS ORDERQNT from clroot.dochdsal
	inner join clroot.litmsale on clroot.dochdsal.aa=clroot.litmsale.documentaa
	inner join clroot.DocParam on clroot.dochdsal.ParamsCode=clroot.DocParam.code
	where clroot.DocParam.updorder=1
	AND clroot.DocParam.stocksign=0
	and clroot.dochdsal.Type1<>3
	and clroot.dochdsal.FlagTrans=0
	and datepart(week,(clroot.dochdsal.date1)) = '40'
	and datepart(year,(clroot.dochdsal.date1)) = '2009'
	GROUP BY clroot.litmsale.linkid,clroot.litmsale.cmmnt,datepart(week,(clroot.dochdsal.date1)),datepart(year,(clroot.dochdsal.date1))";
	if ($result = $pdo->query($sql))
	{
		while($row = $result->fetch())
		{
			echo $row[0] . ":" . $row[1] . "\n";
		}
	}
	else
	{
		echo "ERROR: Could not execute the query. " . print_r($pdo->errorInfo());
	}
	// close connection
	unset($pdo);

and get

Array ( [0] => HY000 [1] => 10024 [2] => SQL Server connection timed out. [10024] (severity 6) [select datepart(week,( clroot.dochdsal .date1)) as weekno,datepart (year,(clroot.d ochdsal.date1)) as yearno,clroot.l itmsale.linkid, SUM(clroot.litm sale.openqnt) AS ORDERQNT from clroot.dochdsal inner join clroot.litmsale on clroot.dochdsal .aa=clroot.litm sale.documentaa inner join clroot.DocParam on clroot.dochdsal .ParamsCode=clr oot.DocParam.co de where clroot.DocParam .updorder=1 AND clroot.DocParam .stocksign=0 and clroot.dochdsal .Type1<>3 and clroot.dochdsal .FlagTrans=0 and datepart(week,( clroot.dochdsal .date1)) = '40' and datepart(year,( clroot.dochdsal .date1)) = '2009' GROUP BY clroot.litmsale .linkid,clroot. litmsale.cmmnt, datepart(week,( clroot.dochdsal .date1)),datepa rt(year,(clroot .dochdsal.date1 ))] [3] => -1 [4] => 6 ) ERROR: Could not execute the query. 1

any ideas?

PS already memory_limit to 300M, mssql.timeout to 1000 and max_execution_t ime to 1000

[ck9663]

Paste your entire into a query analyzer and let's see the error. By just looking, here are some of the problems:

datep art(week,(clroot.dochdsal.date1))

and

datepart(year,(c lroot.dochdsal.date1))] [3] => -1 [4] => 6 )

Happy Coding!!!

--- CK

[mikek12004]

hmmm.....those spaces that appear when doing an echo is not present in the actual query (you can see it in the code) plus in the SQL server 2005 the query runs just fine

[ck9663]

Then it's your connection. Check the property of your connection. How fast does your query run in Query Analyzer?

--- CK

[mikek12004]

query analyzer? I run it in the new query tab of SQL 2005 and it run in about 3 mins

[ck9663]

If it's running properly on the query tab, my guess is it's your connection setting.

Good Luck!!!

--- CK