You are here:

Active Server Pages Programming (ASP)/Execution Problem of a Script

Expert: Srini Nagarajan - 8/18/2005

Question
Hello

We have got a Server in our INTRANET with WINDOWS 2003 and IIS installed for our in-house developed WEB application. Besides IIS is also installed in my computer having Windows 2000. Actually I developed in my m/c, test the script and then give into the Production ( W2003 server ).
Our MAIN server IBM AS400 is lying at Melbourne, Australia and we are using DB2 database. . We used to connect the same with ODBC and fetch the data. This is true for both our W2003 Server and my W2000 machine.
Recently I have added one more feature which is working in my machine. So that same has been given in production where it is giving the following problem.

              Analysis of Credit Note

     Store: All       Business Group: CEG
     Period:   01-07-2005   To:   31-07-2005
                Amount in Lacs

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 84 exceeds limit 30.

?, line 0

Location              REASONS CODE
Total    O.C D.S E.S W.S P.D W.T W.C F.P Q.P D.U D.B W.R M.S R.S U.C
The above line is a heading.

WHAT DOES IT MEAN
Estimated query processing time 84 exceeds limit 30.

Whenever I execute the same program from my machine the actual report appears as follows

Analysis of Credit Note

     Store: All       Business Group: ALL
     Period:   01-07-2005   To:   31-07-2005
                 Amount in Lacs

Location              REASONS CODE
             Total O.C D.S E.S W.S P.D W.T       W.C F.P Q.P D.U D.B W.R M.S R.S U.C

TARATOLLA E.R      0.16 - - - - - - 0.16 - - - - - - - -
JAMSHEDPUR         3.92 - - - - - - - - 1.42 2.49 - - - - -
BHUBHANESWAR       2.75 0.01 - 2.26 0.08 - - 0.03 0.07 - - - 0.31 - - -
DELHI LAJPATNAGAR 0.31 - - - - - 0.31 - - - - - - - - -
SAHIBABAD          0.55 - - 0.12 - - - - - 0.27 - - - 0.16 - -
UDAIPUR            3.67 0.22 - - - - - - 3.45 - - - - - - -
CHANDIGARH         0.41 - - - - - 0.14 0.27 - - - - - - - -
DHANBAD            0.77 - - - - - - - - 0.77 - - - - - -
WEST BOKARO        0.03 - - - - - - - - - - - - - - 0.03
RAMGARH            2.04 - - - - - 1.74 0.01 - - - - 0.22 0.07 - -
JHARSUGUDA         0.11 - - - - - - - - 0.11 - - - - - -
BAGMUNDI, PURULIA 0.62 - 0.62 - - - - - - - - - - - - -
Total       15.34 0.23 0.62 2.38 0.08 - 2.19 0.47 3.52 2.57 2.49 - 0.53 0.23 - 0.03

I am giving the complete listing of the program. Initially I am executing a program misxp18i.asp to take the screen input and then calling misxp18.asp.




<html>
<head>

       <title>Analysis of Credit Note</title>
</head>
<body bgcolor="silver" text="blue">
<h4 align="center">Analysis of Credit Note</h4>
<p align="center">

<%
Server.ScriptTimeout=3000
Dim sCrnt(40,18), sTtl(17)
for i = 0 to 39
  sCrnt(i,0) = ""
  for j = 1 to 16
     sCrnt(i,j) = 0
  next
next

sTtl(0) = "Ttoal"
for i = 1 to 16
   sTtl(i) = 0
next

sDate=request("iDate")
sStrCd=request("iStrCd")
sFrdt=request("iFrdt")
sTodt=request("iTodt")
sBgrp=request("iBgrp")

sFrYy=datepart("yyyy",sFrdt)
sFrMm=datepart("m",sFrdt)
sFrDd=datepart("d",sFrdt)
if sFrMm < 10 then
  sFrMm = "0"&trim(sFrMm)
end if

if sFrDd < 10 then
  sFrDd = "0"&trim(sFrDd)
end if
sFrdt=sFrYy&sFrMm&sFrDd

sToYy=datepart("yyyy",sTodt)
sToMm=datepart("m",sTodt)
sToDd=datepart("d",sTodt)
if sToMm < 10 then
  sToMm = "0"&trim(sToMm)
end if

if sToDd < 10 then
  sToDd = "0"&trim(sToDd)
end if
sTodt=sToYy&sToMm&sToDd

%>

<p align="center">


Store:

<%
sPartSql = " "
sPartSql1 = " "
if isNumeric(sStrCd) then
  if len(trim(sStrCd)) < 2 then
     sXStrCd = "0"&trim(sStrCd)
  else
     sXStrCd = sStrCd
  end if
  sPartSql = sPartSql + " and b.custst ='"&sXStrCd&"' "
  sPartSql1 = sPartSql1 + " and b.custst ='"&sXStrCd&"' "
  response.write sXStrCd
else
  sXStrCd="ALL"
  response.write "All"
end if
%>


Business Group:

<%
if len(trim(sBgrp))>0 then
  select case trim(sBgrp)
      case "A"
         response.write "ALL"
      case "C"
         sPartSql = sPartSql + " and c.cscc='PT' "
         sPartSql1 = sPartSql1 + " and c.cscc='PT' "
         response.write "CEG"
      case "P"
         sPartSql = sPartSql + " and c.cscc='PE' "
         sPartSql1 = sPartSql1 + " and c.cscc='PE' "
         response.write "PSG"
  end select
end if
%>

<br>


Period:

<% response.write right(sFrDt,2) & "-" & mid(sFrdt,5,2) & "-" & left(sFrDt,4) %>

To:

<% response.write right(sToDt,2) & "-" & mid(sTodt,5,2) & "-" & left(sToDt,4) %>

<br>
Amount in Lacs
</p>

<table border="1" align="center">
<tr>
   <th colspan=2><nobr>Location</th>
   <th colspan=14 align="center">REASONS CODE</th>
</tr>
<tr>
   <th></th>
   <th>Total</th>
   <th ID="oc" onmouseover="ocin()" onmouseout="ocout()"><nobr>O.C</th>
   <th ID="ds" onmouseover="dsin()" onmouseout="dsout()"><nobr>D.S</th>
   <th ID="es" onmouseover="esin()" onmouseout="esout()"><nobr>E.S</th>
   <th ID="ws" onmouseover="wsin()" onmouseout="wsout()"><nobr>W.S</th>
   <th ID="pd" onmouseover="pdin()" onmouseout="pdout()"><nobr>P.D</th>
   <th ID="wt" onmouseover="wtin()" onmouseout="wtout()"><nobr>W.T</th>
   <th ID="wc" onmouseover="wcin()" onmouseout="wcout()"><nobr>W.C</th>
   <th ID="fp" onmouseover="fpin()" onmouseout="fpout()"><nobr>F.P</th>
   <th ID="qp" onmouseover="qpin()" onmouseout="qpout()"><nobr>Q.P</th>
   <th ID="du" onmouseover="duin()" onmouseout="duout()"><nobr>D.U</th>
   <th ID="db" onmouseover="dbin()" onmouseout="dbout()"><nobr>D.B</th>
   <th ID="wr" onmouseover="wrin()" onmouseout="wrout()"><nobr>W.R</th>
   <th ID="ms" onmouseover="msin()" onmouseout="msout()"><nobr>M.S</th>
   <th ID="rs" onmouseover="rsin()" onmouseout="rsout()"><nobr>R.S</th>
   <th ID="uc" onmouseover="ucin()" onmouseout="ucout()"><nobr>U.C</th>
<tr>
<%

sSql = "select substr(a.ivno1,3,2),b.locnm,a.rdmpid,sum(a.qty5 * a.unsel) "
sSql = sSql + "from libj25h/mspdetl0 a, libj25usr/uppbrnloc b, libj25h/mspsegs0 c "
sSql = sSql + "where substr(a.ivno1,3,2) = b.brncd and a.ivno1=c.ivno1 and a.rcdtp = 'A' and "
sSql = sSql + "substr(a.ivno1,1,2) = 'IC' "
sSql = sSql + "and a.ivdat8 >= "&sFrdt&" and a.ivdat8 <= "&sTodt&" "
sSql = sSql + sPartSql
sSql = sSql + " group by substr(a.ivno1,3,2),b.locnm,a.rdmpid "
sSql = sSql + " order by substr(a.ivno1,3,2) "

sSql1 = "select substr(a.ivno1,3,2),b.locnm,a.rdmpid,sum(a.qty5 * a.unsel) "
sSql1 = sSql1 + "from libj25/wopindt0 a, libj25usr/uppbrnloc b, libj25h/mspsegs0 c "
sSql1 = sSql1 + "where substr(a.ivno1,3,2) = b.brncd and a.ivno1=c.ivno1 and a.rcdtp = 'A' and "
sSql1 = sSql1 + "substr(a.ivno1,1,2) = 'IC' "
sSql1 = sSql1 + "and a.ivdat8 >= "&sFrdt&" and a.ivdat8 <= "&sTodt&" "
sSql1 = sSql1 + sPartSql1
sSql1 = sSql1 + " group by substr(a.ivno1,3,2),b.locnm,a.rdmpid "
sSql1 = sSql1 + " order by substr(a.ivno1,3,2) "

'response.write "<br>" & ssql & "<br>"
'response.write "<br>" & ssql1 & "<br>"

set sCon = server.createObject("ADODB.Connection")
sCon.open "FILE NAME=C:\XDBS.UDL"
set srs = sCon.execute(sSql)
set srs1 = sCon.execute(sSql1)

i = 0
j = 0
sPntr = 0

while not sRs.EOF
  sTnoFound = "N"
  if sPntr = 0 then
     i = 0
     sPntr = sPntr + 1
  else
     for i = 0 to sPntr - 1
         if sCrnt(i,0) = sRs(1) then
            sTnoFound = "Y"
            exit for
         end if
     next
     if sTnoFound = "N" then
        i = sPntr
        sPntr = sPntr + 1
     end if
  end if
  sCrnt(i,0) = sRs(1)
  select case sRs(2)
       case "OC"
               j = 2
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "DS"
               j = 3
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "ES"
               j = 4
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "WS"
               j = 5
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "PD"
               j = 6
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "WT"
               j = 7
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "WC"
               j = 8
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "FP"
               j = 9
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "QP"
               j = 10
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "DU"
               j = 11
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "DB"
               j = 12
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "WR"
               j = 13
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "MS"
               j = 14
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
       case "RS"
               j = 15
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))

       case else
               j = 16
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs(3))
  end select
  sCrnt(i,17) = sRs(0)
  sRs.MoveNext
wend

sPntr = i
i = 0
k = 0

while not sRs1.EOF
  sTnoFound = "N"
  for i = 0 to sPntr
      if sCrnt(i,0) = sRs1(1) then
         sTnoFound = "Y"
         exit for
      end if
  next
  if sTnoFound = "N" then
     sPntr = sPntr + 1
     i = sPntr
     sCrnt(i,0) = sRs1(1)
     sCrnt(i,17) = sRs1(0)
  end if

  select case sRs1(2)
       case "OC"
               j = 2
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "DS"
               j = 3
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "ES"
               j = 4
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "WS"
               j = 5
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "PD"
               j = 6
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "WT"
               j = 7
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "WC"
               j = 8
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "FP"
               j = 9
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "QP"
               j = 10
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "DU"
               j = 11
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "DB"
               j = 12
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "WR"
               j = 13
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "MS"
               j = 14
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
       case "RS"
               j = 15
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))

       case else
               j = 16
               sCrnt(i,j) = sCrnt(I,j) + cDbl(sRs1(3))
               sCrnt(i,1) = sCrnt(I,1) + cDbl(sRs1(3))
  end select
  sRs1.MoveNext
wend

sCon.close
sFactor = 1/100000

for i = 0 to 39
   if len(trim(sCrnt(i,0))) > 0 then %>
      <tr>
          <td><nobr><% =sCrnt(i,0) %></td>
          <%
          for j = 1 to 16
              xAmt = (round(sCrnt(i,j) * sFactor,2)) * (-1)
              sTtl(j) = sTtl(j) + xAmt
              if xAmt <> 0 then %>
                 <td style="text-align:center"><%=xAmt%></td><%
              else %>
                 <td style="text-align:center">-</td><%
              end if
          next %>
      </tr><%
   end if
next%>

<tr>
   <th>Total</th>
<%
   for j = 1 to 16
       if sTtl(j) > 0 then %>
          <td style="text-align:center"><% =sTtl(j) %></td><%
       else %>
          <td style="text-align:center">-</td>
  <%end if
   next
%>
</tr>
</table>
</body>

<script language="JavaScript">

function ocin()
{
oc.innerText="Order Cancellation";
}
function ocout()
{
oc.innerText="O.C";
}

function dsin()
{
ds.innerText="Late Delivery";
}

function dsout()
{
ds.innerText="D.S";
}

function esin()
{
es.innerText="Excess Supply";
}
function esout()
{
es.innerText="E.S";
}

function wsin()
{
ws.innerText="Wrong Supply";
}
function wsout()
{
ws.innerText="W.S";
}

function pdin()
{
pd.innerText="Price Difference";
}

function pdout()
{
pd.innerText="P.D";
}

function wtin()
{
wt.innerText="Wrong Recommendation by TIL";
}

function wtout()
{
wt.innerText="W.T";
}

function wcin()
{
wc.innerText="Wrong Recommendation by Customer";
}
function wcout()
{
wc.innerText="W.C";
}

function fpin()
{
fp.innerText="Fitment Problem";
}

function fpout()
{
fp.innerText="F.P";
}

function qpin()
{
qp.innerText="Quality Problem";
}
function qpout()
{
qp.innerText="W.P";
}

function duin()
{
du.innerText="Wrong Entry in DBS";
}

function duout()
{
du.innerText="D.U";
}

function dbin()
{
db.innerText="Excess Ordering by B/O Analyst";
}
function dbout()
{
db.innerText="D.B";
}

function wrin()
{
wr.innerText="Wrong Tax Allocation";
}

function wrout()
{
wr.innerText="W.R";
}

function msin()
{
ms.innerText="Wrong Misc. Charged";
}

function msout()
{
ms.innerText="M.S";
}

function rsin()
{
rs.innerText="Return from SubDealer";
}

function rsout()
{
rs.innerText="R.S";
}

function ucin()
{
uc.innerText="Unclassified";
}

function ucout()
{
uc.innerText="U.C";
}
</script>
</html>

Hope I am able to explain you my problem ad request you to provide the solution.

Thanks and Regards

Biswasarathi Ghosh

Answer
Hi

Sorry for the Delay

Here is the solution first
Open the ODBC Data Source Administrator on the client machine that is experiencing the SQL0666 error

In the Administrator window, highlight the DSN that you want to change and click on the Configure button

Click on the Performance tab inside the iSeries Access for Windows Setup dialogue that appears

Click on the Advanced button under the Performance options. This will bring up the Advanced performance options window
Turn off the checkmark in the Allow Query Timeout checkbox. Click on OK to exit this screen

Back on the iSeries Access for Windows Setup screen, click on the Apply button followed by the OK button. This allows you to exit the screen and save your changes

Here is the explanation

Where xxx and yyy represent the estimated amount of time it will take to process the query and the OS/400 query time limit, respectively. OS/400's query time limit is kept in the Query processing time limit system value, QQRYTIMLMT, which can be viewed or changed through the following Work with System Values command (WRKSYSVAL):

WRKSYSVAL SYSVAL(QQRYTIMLMT)

This system value is important for SQL processing because--when a query is submitted to OS/400--the query optimizer estimates a value for the elapsed number of seconds that the query is expected to run (as represented by xxx in the error message). This value is compared against the query time limit (as represented in QQRYTIMLMT and in the yyy value returned in the SQL0666 message). If the xxx value is greater than the yyy value, OS/400 will not allow the query to start.

In pre-V5R2M0 versions of iSeries Access for Windows' ODBC, query timeout value support can be disabled in applications that use ADO by setting the CommandTimeout property to 0. This technique is explained in greater detail in the articles listed in the Related Stories section.

Happy Programming!

-Srini

Questioner's Rating

Rating(1-10)	Knowledgeability = 10	Clarity of Response = 10	Politeness = 10
Comment	Thanks a lot Srini!

Add to this Answer
Ask a Question

Active Server Pages Programming (ASP)

All Answers

Answers by Expert:

Ask Experts

Volunteer

Srini Nagarajan

Expertise

I can answer any kind of questions in ASP.NET, C#, VB.NET, SharePoint 2007, ASP, Coldfusion, Powerbuilder 7.00 / 8.00, JAVA servlets, MS SQL 2000 / MSSQL7, Sybase

Experience

Contact me if you need any custom development on ASP.NET, ASP, SharePoint 2007, Coldfusion, Powerbuilder.

User Agreement
Privacy Policy
Encyclopedia
©2012 About.com, a part of The New York Times Company. All rights reserved.

About Us
Tell Friends
How to Ask
How to Volunteer
FAQ
Tech Support
Top Experts
Expert Login
Browse Answers:
By Category
Alphabetically
Find expert answers to:

Active Server Pages Programming (ASP)/Execution Problem of a Script

Related Articles

Srini Nagarajan

Expertise

Experience