By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

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

mikek12004
100+
P: 200
doing a (not so simple) query -which works fine in sql server using PDO like this:
Expand|Select|Wrap|Line Numbers
  1. // attempt a connection
  2.     try {
  3.         $pdo = new PDO("mssql:host=$hostname,1433;dbname=$dbname;",$username,$password);
  4.     } catch (PDOException $e) {
  5.     die("ERROR: Could not connect: " . $e->getMessage());
  6.     }
  7.     // create and execute SELECT query
  8.     $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
  9.     inner join clroot.litmsale on clroot.dochdsal.aa=clroot.litmsale.documentaa
  10.     inner join clroot.DocParam on clroot.dochdsal.ParamsCode=clroot.DocParam.code
  11.     where clroot.DocParam.updorder=1
  12.     AND clroot.DocParam.stocksign=0
  13.     and clroot.dochdsal.Type1<>3
  14.     and clroot.dochdsal.FlagTrans=0
  15.     and datepart(week,(clroot.dochdsal.date1)) = '40'
  16.     and datepart(year,(clroot.dochdsal.date1)) = '2009'
  17.     GROUP BY clroot.litmsale.linkid,clroot.litmsale.cmmnt,datepart(week,(clroot.dochdsal.date1)),datepart(year,(clroot.dochdsal.date1))";
  18.     if ($result = $pdo->query($sql))
  19.     {
  20.         while($row = $result->fetch())
  21.         {
  22.             echo $row[0] . ":" . $row[1] . "\n";
  23.         }
  24.     }
  25.     else
  26.     {
  27.         echo "ERROR: Could not execute the query. " . print_r($pdo->errorInfo());
  28.     }
  29.     // close connection
  30.     unset($pdo);
  31.  
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
Dec 14 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Paste your entire into a query analyzer and let's see the error. By just looking, here are some of the problems:

Expand|Select|Wrap|Line Numbers
  1.  
  2. datep art(week,(clroot.dochdsal.date1))
  3.  
  4.  
and

Expand|Select|Wrap|Line Numbers
  1.  
  2. datepart(year,(c lroot.dochdsal.date1))] [3] => -1 [4] => 6 ) 
  3.  
  4.  

Happy Coding!!!

--- CK
Dec 14 '09 #2

mikek12004
100+
P: 200
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
Dec 15 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Then it's your connection. Check the property of your connection. How fast does your query run in Query Analyzer?

--- CK
Dec 15 '09 #4

mikek12004
100+
P: 200
query analyzer? I run it in the new query tab of SQL 2005 and it run in about 3 mins
Dec 15 '09 #5

ck9663
Expert 2.5K+
P: 2,878
If it's running properly on the query tab, my guess is it's your connection setting.

Good Luck!!!

--- CK
Dec 16 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.