467,911 Members | 1,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,911 developers. It's quick & easy.

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

mikek12004
100+
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
  • viewed: 2290
Share:
5 Replies
ck9663
Expert 2GB
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+
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 2GB
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+
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 2GB
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.

Similar topics

4 posts views Thread by Stefano | last post: by
4 posts views Thread by jas | last post: by
3 posts views Thread by Nils Magnus Englund | last post: by
1 post views Thread by traceable1 | last post: by
3 posts views Thread by rcamarda | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.