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.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,datep art(week,(clroot.dochdsal.date1)),datepart(year,(c lroot.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_time 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