server/client side cursors

#1 (**permalink**) 02-27-2008, 10:04 PM

hi all,
we are tunning an asp 6 appliocation, running on sql server 2000.

running the profiler shows that some of the cursors are running on the
server side (instead of running on the client). eventhough - in all cursor
definitions the
CursorLocation is set to 3.

I guess it might be happening as nested cursors are issued.
but I'm not certin.
is that the case here? and is there a way to workaroung it?

I'm enclosing the a small example of a cursor as captured by the profiler
followed by the application code that runs this cursor:

profiler

you can copy&paste into notepad)
32 10 declare @P1 int
set @P1=180150006
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=12
exec sp_cursoropen @P1 output, N'SELECT b.*,c.autFieldName FROM genDocFormat
AS b LEFT JOIN genAutFields c on b.inpFieldName = c.autFieldName AND
c.autGroup = ''513'' AND c.autUser = 62 WHERE b.InpNumber = ''513'' AND
b.inpFieldForm = ''B'' ORDER BY b.InpFieldForm,b.inpTxtTabIndex', @P2 output,
@P3 output, @P4 output
select @P1, @P2, @P3, @P4

33 10
exec sp_cursorfetch 180150006, 2, 0, 1

34 10 exec sp_cursorfetch 180150006, 2, 0, 1

35 10 exec sp_cursorfetch
180150006, 2, 0, 1
36 10
exec sp_cursorfetch 180150006, 2, 0, 1

37 10 exec sp_cursorfetch 180150006, 2, 0, 1

38 10 exec
sp_cursorfetch 180150006, 2, 0, 1

39 10 exec sp_cursorfetch 180150006, 2, 0, 1

40 10 exec sp_cursorfetch
180150006, 2, 0, 1
41 10
exec sp_cursorfetch 180150006, 2, 0, 1

42 10 exec sp_cursorfetch 180150006, 2, 0, 1

43 10 exec
sp_cursorfetch 180150006, 2, 0, 1

65 10 exec sp_cursorfetch 180150004, 2, 0, 1

and the asp code:
<%@ Language=VBScript %>

<SCRIPT language=javaScript>

function setStatus(obj)
{
if (obj.checked)
val = "1"
else
val = "NULL"
url = "docViewSetStatus.asp?CMP=" + company.value
+ "&ID=" + obj.id
+ "&VAL=" + val
+ "&G=" + group.value
+ "&N=" + number.value

window.showModalDialog(url,window,"status:no;help: 0;dialogheight:0;dialogwidth:0")
}

</SCRIPT>

<HTML>
<HEAD>
<META http-equiv="expires" content="-1">
<META http-equiv="Content-Type" content="text/html;charset=windows-1255">
<LINK href="/<%=Request("CMP")%>/doc.css" rel="stylesheet" type="text/css">
</HEAD>
<BODY id=vBody class=viewBody bottommargin=0 topmargin=0 onload=this.focus()>
<FORM name="MainForm">
<%
Dim cn
Dim rs
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "driver={SQL Server};server=(local);uid=sa;pwd=;database=" &
Request("CMP")
on error resume next
if Request.QueryString("N") <> "" then
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.ActiveConnection = cn
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.CursorLocation = 3
rs2.ActiveConnection = cn
sql = "EXEC B_HeaderView_" & Request("G") &_
" '" & Request("G") & "'," & Request("N")
rs2.Open sql,cn
if not rs2.EOF then
group_ = Request("G")
usercode_ = Request.Cookies("usercode")
sql = "SELECT b.*,c.autFieldName" &_
" FROM genDocFormat AS b" &_
" LEFT JOIN genAutFields c on b.inpFieldName = c.autFieldName" &_
" AND c.autGroup = '" & group_ & "' AND c.autUser = " & usercode_ &_
" WHERE b.InpNumber = '" & group_ & "' AND b.inpFieldForm = 'H'" &_
" ORDER BY b.InpFieldForm,b.inpTxtTabIndex"
rs.Open sql ,cn
if not rs.EOF then
do while not rs.EOF
TI = rs("InpTxtTabIndex")
FT = rs("inpFieldType")
N = rs("InpFieldName")
if not isNull(TI) then
if FT <> "H" then
Response.Write "<INPUT Type=hidden name=" & rs("inpFieldName") &
" value=" & FT & " must='" & rs("inpMust") & "'>"
end if
FO = rs("inpTxtTabIndex")
L = rs("InpTxtLeft") - 200

DL = rs("InpDivLeft") - 200
DT = rs("InpDivTop")

LL = rs("InpLblLeft") - 200
if isnull(rs("autFieldName")) then
LW = rs("inpLblWidth")
else
LW = 0
end if
LA = rs("InpLblAlign")
LC = rs("InpLblContent")

V = rs("InpTxtValue")
TH = rs("InpTxtHeight")
if isnull(rs("autFieldName")) then
TW = rs("inpTxtWidth")
else
TW = 0
end if
BL = rs("InpBtnLeft") - 200
SL = rs("InpShoLeft") - 200
if isnull(rs("autFieldName")) then
SW = rs("inpShoWidth")
else
SW = 0
end if
if rs("inpFieldType") = "M" then
D = "ltr"
TA = "right"
else
if rs("InpTxtDir") = 1 then
D = "ltr"
TA = "left"
else
D = "rtl"
TA = "right"
end if
end if

select case FT
case "A" : 'ÃºÃ*Ã¥Ã¸ Ã°Ã¸Ã§Ã¡/TextArea
Response.Write "<TEXTAREA class=hdrInput name=" & N & " dir=" & D
if not isNull(rs("inpLock")) and rs("inpLock") = "1" then
Response.Write " readOnly"
Response.Write " style='POSITION:absolute;LEFT:"& L &"px;TOP:" &
DT & "px;"
Response.Write "WIDTH:" & TW & "px;HEIGHT:" & TH & "px;'>"
Response.Write "</TEXTAREA>"
case "B" : ' Ã«Ã´ÃºÃ¥Ã¸/Button
Response.Write "<INPUT disabled Type=Button name=" & N & "
value='" & LC & "'"
Response.Write " style='position:absolute; left:" & DL & "px;
width:" & TW & "px;"
Response.Write " top:" & DT & "px; height:" & TH & "px;
font-family:arial; font-size:14px'"
Response.Write ">"
LW = 0
case "C" :
Response.Write "<INPUT type=checkbox id=" & N & " dir=" & D &_
" style='position:absolute;left:" & L & "px;width:" & TW & "px;"
&_
"top:" & DT & "px;height:" & TH &
"px;font-family:arial;font-size:14px'" &_
" onclick='window.event.returnValue=false'"
if not isNull(rs2(N)) then
Response.Write " CHECKED"
end if
Response.Write ">"
case "E" :
Response.Write "<INPUT type=checkbox id=" & N & " dir=" & D &_
" style='position:absolute;left:" & DL & "px;width:" & TW &
"px;" &_
"top:" & DT & "px;height:" & TH &
"px;font-family:arial;font-size:14px'" &_
" onclick=setStatus(this)"
if not isNull(rs2(N)) then
Response.Write " CHECKED"
end if
Response.Write ">"
case "H" : 'hidden
on error resume next
val = rs2(N)
if err.number = 0 then
Response.Write "<INPUT type=hidden id=" & N & " value='" & val
& "'>"
else
Response.Write "<INPUT type=hidden id=" & N & " value='" & V &
"'>"
end if
LW = 0
case "I" : ' ÃºÃ®Ã¥Ã°Ã¤/Image
Response.Write "<IMG border=1 name=" & N
Response.Write " style='POSITION:absolute; LEFT:"& L &"px;
TOP:"& DT & "px;"
Response.Write " WIDTH:"& TW &"px; HEIGHT:" & TH & "px;'>"
case "S" :
Response.Write "<SELECT class=hdrInput name=" & N & " tabindex="
& TI & " readonly dir=" & D
Response.Write " style='POSITION:absolute;LEFT:" & L &
"px;WIDTH:" & TW & "px;"
Response.Write "TOP:" & DT & "px;HEIGHT:20px;'>"
if N = "hdrCreditType" then
Response.Write "<OPTION value=1 selected >Ã¸Ã¢Ã©Ã¬</OPTION>"
Response.Write "<OPTION value=2 >Ã¹Ã¥Ã¨Ã³</OPTION>"
end if
Response.Write "</SELECT>"
case "T" :' Ã¨Ã¡Ã¬Ã¤/Table
Response.Write "<INPUT id=" & N & " class=hdrInput readonly
name=" & N & " dir=" & D
on error resume next
Response.Write " value='" & rs2(N) & "'"
if err.number <> 0 then
Response.Write " value='" & N & "'"
end if
on error goto 0
Response.Write " style='POSITION:absolute; LEFT:" & L & "px;
TOP:" & DT & "px;"
Response.Write " WIDTH:" & TW & "px; HEIGHT:" & TH & "px;"
Response.Write " font-family:arial; font-size:14px;
background-Color:azure;'>"
Response.Write "<INPUT id=" & N & "Dsp readonly class=copyInput
name=" & N & "Dsp dir=rtl"
tblDesc = N & "Dsp"
on error resume next
Response.Write " value='" & rs2(tblDesc) & "'"
if err.number <> 0 then
Response.Write " value='" & tblDesc & "'"
end if
on error goto 0
Response.Write " style='POSITION: Absolute; LEFT:" & SL & "px;
TOP: " & DT & "px;"
Response.Write " WIDTH:" & SW & "px; HEIGHT:" & TH & "px;
background-Color:azure;'>"
case "K" : ' Ã®Ã´ÃºÃ§/Key
Response.Write "<INPUT id=" & N & " class=hdrInput name=" & N &
" tabindex=" & TI & " dir=" & D
Response.Write " value='" & rs2(N) & "' readonly"
Response.Write " style='POSITION:absolute; LEFT:" & L & "px;
TOP:" & DT & "px;"
Response.Write " WIDTH:" & TW & "px; HEIGHT: " & TH & "px;"
Response.Write " background-Color:azure; font-family:arial;
font-size:14px'>"
case "Y" :
Response.Write "<INPUT class=hdrInput id=" & N & " name=" & N &
" tabindex=" & TI & " dir=" & D
Response.Write " readonly"
if not isnull(rs2(N)) then
Response.Write " value='" & rs2(N) & "'"
end if
Response.Write " style='POSITION:absolute; LEFT:" & L & "px;
TOP:" & DT & "px;"
Response.Write "WIDTH:" & TW & "px; HEIGHT: " & TH &
"px;text-align:" & TA & "'>"
case "Z" :
Response.Write "<INPUT class=hdrInput id=" & N & " name=" & N &
" tabindex=" & TI & " dir=" & D
Response.Write " readonly"
if not isnull(rs2(N)) then
Response.Write " value='" & FormatDateTime(rs2(N),3) & "'"
end if
Response.Write " style='POSITION:absolute; LEFT:" & L & "px;
TOP:" & DT & "px;"
Response.Write "WIDTH:" & TW & "px; HEIGHT: " & TH &
"px;text-align:" & TA & "'>"
case else :
Response.Write "<INPUT class=hdrInput id=" & N & " name=" & N &
" tabindex=" & TI & " dir=" & D
Response.Write " readonly"
on error resume next
if not isNull(rs2(N)) then
select case rs2(N).Type
case 6 :
if isnull(rs("inpDecimal")) then
dec = 2
else
dec = rs("inpDecimal")
end if
Response.Write " value='" & FormatNumber(rs2(N),dec) & "'"
case 135 :
Response.Write " value='" & mid(rs2(N),1,6) &
mid(rs2(N),9,2) & "'"
case else
Response.Write " value='" & rs2(N) & "'"
end select
end if
if err.number <> 0 then
Response.Write " value='" & tblDesc & "'"
end if
on error goto 0
Response.Write " style='POSITION:absolute; LEFT:" & L & "px;
TOP:" & DT & "px;"
Response.Write "WIDTH:" & TW & "px; HEIGHT: " & TH &
"px;text-align:" & TA & "'>"
end select
if LW <> 0 then
Response.Write "<NOBR><LABEL class=hdrLable dir=rtl"
Response.Write " style='POSITION:absolute;TOP:" & (DT + 2) &
"px;LEFT:" & LL & "px;"
Response.Write "WIDTH:" & LW & "px;HEIGHT:20px;"
select case LA
case 1
Response.Write " text-align:left"
case 2
Response.Write " text-align:right"
case else
Response.Write " text-align:center"
end select
Response.Write "'><B>" & LC & "</B></LABEL></NOBR>"
end if
end if
rs.MoveNext
loop
end if
Response.Write "<INPUT type=hidden name=FNumber value=" +
Request.QueryString("G") + ">"
rs.Close
end if
rs2.Close
set rs = nothing
cn.Close
set cn = nothing
end if
%>
</FORM>
<INPUT type=hidden id=company value=<%=Request("CMP")%>>
<INPUT type=hidden id=group value=<%=Request("G")%>>
<INPUT type=hidden id=number value=<%=Request("N")%>>
</BODY>
</HTML>

thanks,
tom.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode