I'm using ASP (VB Script) to generate some reports from a SQL Server
database. These queries often take a significant amount of time to complete,
and many of these reports consist of multiple queries.
One of the biggest problems end users have with this is that if the report
generation is cancelled (e.g. hitting the stop button on the browser) SQL
Server continues to process the request, which usually renders the server
unresponsive until the query completes.
I tried using Response.IsClie ntConnected to detect whether the client is
still connected to the server, and if not, I call cancel on the RecordSet
object. Unfortunately, this doesn't seem to work, and I'm trying to
understand why. Any advice as to why this doesn't do what I expect would be
greatly appreciated.
Here is the code I'm executing:
for i = 0 to 20
strSQL = objRpt.Subrepor tSQL(cstr(i)).S QLString
set rsts(i) = CreateObject("A DODB.Recordset" )
strSQL = objRpt.ParseSQL (strSQL,strpara mnames,strParam Values)
err.Clear
done = false
rsts(i).CursorT ype = 3 ' adOpenStatic
rsts(i).LockTyp e = 1 ' adLockReadOnly
rsts(i).CursorL ocation = 3 ' adUseClient
rsts(i).Open strSQL,whconn', ,adAsyncConnect
Do
if err <> 0 then
for each ce in whconn.Errors
Response.Write "Error - " & ce.NativeError & " - " &
ce.Description & "<br>"
next
Response.End
else
if not done then
Response.Write " <!-- Success -->" & vbcrlf
done = true
end if
end if
if not Response.IsClie ntConnected then
rsts(i).cancel
Response.Write "<!-- cancelled -->" & vbcrlf
exit Do
end if
AspSleep(2)
Loop
next
-----------------------
Thanks again for any advice,
-Gary
database. These queries often take a significant amount of time to complete,
and many of these reports consist of multiple queries.
One of the biggest problems end users have with this is that if the report
generation is cancelled (e.g. hitting the stop button on the browser) SQL
Server continues to process the request, which usually renders the server
unresponsive until the query completes.
I tried using Response.IsClie ntConnected to detect whether the client is
still connected to the server, and if not, I call cancel on the RecordSet
object. Unfortunately, this doesn't seem to work, and I'm trying to
understand why. Any advice as to why this doesn't do what I expect would be
greatly appreciated.
Here is the code I'm executing:
for i = 0 to 20
strSQL = objRpt.Subrepor tSQL(cstr(i)).S QLString
set rsts(i) = CreateObject("A DODB.Recordset" )
strSQL = objRpt.ParseSQL (strSQL,strpara mnames,strParam Values)
err.Clear
done = false
rsts(i).CursorT ype = 3 ' adOpenStatic
rsts(i).LockTyp e = 1 ' adLockReadOnly
rsts(i).CursorL ocation = 3 ' adUseClient
rsts(i).Open strSQL,whconn', ,adAsyncConnect
Do
if err <> 0 then
for each ce in whconn.Errors
Response.Write "Error - " & ce.NativeError & " - " &
ce.Description & "<br>"
next
Response.End
else
if not done then
Response.Write " <!-- Success -->" & vbcrlf
done = true
end if
end if
if not Response.IsClie ntConnected then
rsts(i).cancel
Response.Write "<!-- cancelled -->" & vbcrlf
exit Do
end if
AspSleep(2)
Loop
next
-----------------------
Thanks again for any advice,
-Gary
Comment