Query causing Response Buffer to exceed its configured limit 1

[cdck]

I have an ASP page which takes a record from SQL Server and generates output with additional data from other tables/views on the same server. Specifically, it pulls the information on a specific box in the archives, and then fleshes that information out further by expanding references - like the User ID becoming a user name, and the department number becoming a department name.

The queries which pull the additional data are embedded at the points in the HTML where their results need to be displayed. The query which displays the user name pulls from a table with 80 users, and displays fine. However, when I use the exact same processing for the department number, which can pull either from a view with 11 records or a table with 18 (I've tried it either way) I get:
[tt]Response object error 'ASP 0251 : 80004005'

Response Buffer Limit Exceeded

/Accounting/FIA001printFrame.asp, line 0

Execution of the ASP page caused the Response Buffer to exceed its configured limit. [/tt]

The original query is pulled thus:

<%

listSQL="SELECT ID, FileNo, Location, Dept, Contents, DestDate, DestMeth, TimePeriod, Disposition, Destroyed, PackedBy FROM FileStorageRec WHERE ID = " & BoxID & ";"

Set Con=Server.CreateObject("ADODB.Connection")
Con.Open strConnect
set rstemp= Con.Execute(listSQL)

If rstemp.eof Then response.write("") End If

BoxNo = rstemp(1)
Loca = rstemp(2)
DeptNo = CInt(rstemp(3))
Descr = rstemp(4)
DestDate = rstemp(5)
DestMeth = rstemp(6)
TimePeriod = rstemp(7)
Dispo = rstemp(8)
Destroyed = rstemp(9)
PackedBy = rstemp(10)

rstemp.Close
Set rstemp = Nothing
Con.Close
Set Con = Nothing
%>

DeptNo is then used to pull the Department name. I use CInt on the variable as I set it, because until I did so, I got type mismatch errors, even though the data is stored in the SQL table as the same numeric type in both FileStorageRec and the department list view (vwITdepartmentList):

<%
If DeptNo > 0 Then
  userSQL = "SELECT GrpName FROM vwITdepartmentList WHERE GrpDep = " & DeptNo & ";"
				
  Set Con=Server.CreateObject("ADODB.Connection")
  Con.Open strConnect
  set rstempU= Con.Execute(userSQL)

  If rstempU.eof Then response.write("RECORDS") End If

  Do Until rstempU.eof
			
  Unm = rstempU(0)

  Response.Write(Unm)

  Loop

  rstempU.Close
  Set rstempU = Nothing
  Con.Close
  Set Con = Nothing
Else
  Response.Write("RECORDS")
End If
%>

If I remove the above section of code from the page, further down in the code I have the following, which properly pulls and displays the user's name based on the PackedBy variable:

<%
If Len(PackedBy) > 0 Then
  userSQL = "SELECT UID, LName, FName FROM UserInfo WHERE UID = " & PackedBy & " ORDER BY Lname, Fname;"
				
  Set Con=Server.CreateObject("ADODB.Connection")
  Con.Open strConnect
  set rstempU= Con.Execute(userSQL)

  If rstempU.eof Then response.write "" End If

  Do Until rstempU.eof
			
  Uid = rstempU(0)
  Unm = rstempU(1) & ", " & rstempU(2)

  Response.Write(Unm & "</P>")

  Loop

  rstempU.Close
  Set rstempU = Nothing
  Con.Close
  Set Con = Nothing
Else
  Response.Write("unknown")
End If
%>

I have verified that the DeptNo data is the same type in both affected tables. I have attempted pulling the information directly from the table as opposed to the view. All of the sources I've found online which had this issue were dealing with very large datasets, whereas mine is very, very small. Removing the code to pull the department name causes the remainder of the page to fire correctly, including the username section.

I have also attempted removing the DeptNo variable, and hard-coding a department number into the query, with the same results. Clearly, the issue is in the query, itself. I've verified that the SQL login used through the connection string has the same permissions to the department view and table that it has to the UserInfo table.

Any ideas?


Cheryl dc Kern

 

[MarkSweetland]

Response Buffer exceeding the configured limit usually results from too much being dumped to the client.

Where is it that you are incrementing the recordset rstempU ? Could it be that you're stuck in an infinite loop?


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[cdck]

Mark:

Outside of the script, I had no loop, so that wasn't the issue.

Inside the script I had a loop to process the result set on the off chance that more than one record was returned; however, if the data is entered properly (and it's my job to keep if from being entered improperly) there should only ever be one result. So I removed that loop just to see what would happen.

The page loads just fine now.

Thanks for drawing my attention in the right direction!





Cheryl dc Kern