• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1230
  • Last Modified:

VBScript Output text/xml - Execution of the ASP page caused the Response Buffer to exceed its configured limit

Hello,

I'm trying to output an XML feed that contains 25000 records, my environment is -

ASP VBScript
MS SQL 2008 database
W2K8 Server

When I test the XML feed, I get the following error -

/feeds/indeed2.asp Line Number: 74
Description: Response Buffer Limit Exceeded
Category: Response object
ASP Description: Execution of the ASP page caused the Response Buffer to exceed its configured limit.

This is my code -

<%@LANGUAGE="VBSCRIPT"%><?xml version="1.0" encoding="ISO-8859-1"?>
<%
Server.ScriptTimeout=950
%>
<!--#include virtual="/Connections/recruta2.asp" -->
<% 
Response.Buffer = true
Response.ContentType = "text/xml"
   
Dim searchresults
Dim searchresults_cmd
Dim searchresults_numRows

Set searchresults_cmd = Server.CreateObject ("ADODB.Command")
searchresults_cmd.ActiveConnection = MM_recruta2_STRING
searchresults_cmd.CommandText = "SELECT A.JBAID, A.JBATitle, A.JBALocation, A.JBACategory, A.JBAPayRate, A.JBADescription, A.JBAEmplymentType, A.JBAFeaturedJob, CONVERT(CHAR(11),A.JBADatePosted,106) AS JBADatePosted, C.JBCLName, S.JBSURL, S.JBSURLShort, S.JBSRegion, CASE WHEN A.JBADatePosted >= DATEADD(d,-7,GETDATE()) THEN 'Y' ELSE 'N' END AS sponsored FROM dbo.JBAdvert A inner join dbo.JBClient C on A.JBAClientID = C.JBCLID inner join dbo.JBSite S on A.JBASiteID = S.JBSSiteID order by JBSRegion, JBAID desc" 
searchresults_cmd.Prepared = true

Set searchresults = searchresults_cmd.Execute
searchresults_numRows = 0
arrSR = searchresults.GetRows() 'Get the results into a 2-dimensional array
			
searchresults.Close() 'Clean Up
Set searchresults = Nothing 'Clean Up
Set searchresults_cmd = Nothing 'Clean Up

Function RemoveChar(strInput)
    Set reg = New RegExp

    With reg
        .Pattern = "[^a-zA-Z0-9.<>?/@,.:!""£$&()_+=;#' \r-]"
        .Global = True
        result = .Replace(strInput, "")
    End With 

    RemoveChar = result
End Function  

Function ApplyXMLFormatting(strInput)
  strInput = Replace(strInput,"&", "&amp;")
  strInput = Replace(strInput,"&amp;amp;", "&amp;")
  strInput = Replace(strInput,"&#163;","&pound;")
  strInput = Replace(strInput,"&amp;#163;","&pound;")
  strInput = Replace(strInput,"&#169;","&copy;")
  strInput = Replace(strInput,"&amp;#169;","&copy;")  
  strInput = Replace(strInput,"'", "&apos;")
  strInput = Replace(strInput,"""", "&quot;")
  strInput = Replace(strInput, ">", "&gt;")
  strInput = Replace(strInput,"<","&lt;")
  ApplyXMLFormatting = strInput
End Function   

With response

.write("<source>" & VbCrLf)
.write("<publisher></publisher>" & VbCrLf)
.write("<publisherurl></publisherurl>" & VbCrLf)

End With 

If IsArray(arrSR) Then
Dim i
For i = 0 To UBound(arrSR, 2)

With response

.write("<job>" & VbCrLf)
.write("<source><![CDATA[" & (arrSR(11,i))& "]]></source>" & VbCrLf)
.write("<title><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(1,i)))& " - " & ApplyXMLFormatting(RemoveChar(arrSR(2,i)))& "]]></title>" & VbCrLf)
.write("<date><![CDATA[" & (arrSR(8,i))& "]]></date>" & VbCrLf)
.write("<referencenumber><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(0,i)))& "]]></referencenumber>" & VbCrLf)
.write("<url><![CDATA[" & (arrSR(10,i))& "/detail.asp?ID=" & (arrSR(0,i))& "]]></url>" & VbCrLf)
.write("<company><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(9,i)))& "]]></company>" & VbCrLf)
.write("<city><![CDATA[" & (arrSR(2,i))& "]]></city>" & VbCrLf)
.write("<country>UK</country>" & VbCrLf)
.write("<description><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(5,i)))& "]]></description>" & VbCrLf)
.write("<salary><![CDATA[" & ApplyXMLFormatting((arrSR(4,i)))& "]]></salary>" & VbCrLf)
.write("<jobtype><![CDATA[" & (arrSR(6,i))& "]]></jobtype>" & VbCrLf)
.write("<category><![CDATA[" & (arrSR(3,i))& "]]></category>" & VbCrLf)
.write("<sponsored><![CDATA[" & VbCrLf)

IF arrSR(7,i) = "Y" AND arrSR(13,i) = "Y" then 
.write("1" & VbCrLf)
End IF

.write("</sponsored>" & VbCrLf)
.write("</job>" & VbCrLf)
End With 
next
End if 

With response
.write("</source>" & VbCrLf)
End With
%>

Open in new window


I could really do with putting together a streamlined slim verion of this, that doesn't exceed the buffer limit, but that removes all HTML tags and all non XML charaters before outputting as XML.

Also what would be good would be if it loaded the records as there were read...

And didn't consume all of the server resources...

Am I asking too much???

Please help
0
garethtnash
Asked:
garethtnash
1 Solution
 
hieloCommented:
try:
<%@LANGUAGE="VBSCRIPT"%>
<%
Response.Buffer = False
Response.ContentType = "text/xml"
Server.ScriptTimeout=950
%><?xml version="1.0" encoding="ISO-8859-1"?>
<!--#include virtual="/Connections/recruta2.asp" -->
<% 
   
Dim searchresults
Dim searchresults_cmd

Set searchresults_cmd = Server.CreateObject ("ADODB.Command")
searchresults_cmd.ActiveConnection = MM_recruta2_STRING
searchresults_cmd.CommandText = "SELECT A.JBAID, A.JBATitle, A.JBALocation, A.JBACategory, A.JBAPayRate, A.JBADescription, A.JBAEmplymentType, A.JBAFeaturedJob, CONVERT(CHAR(11),A.JBADatePosted,106) AS JBADatePosted, C.JBCLName, S.JBSURL, S.JBSURLShort, S.JBSRegion, CASE WHEN A.JBADatePosted >= DATEADD(d,-7,GETDATE()) THEN 'Y' ELSE 'N' END AS sponsored FROM dbo.JBAdvert A inner join dbo.JBClient C on A.JBAClientID = C.JBCLID inner join dbo.JBSite S on A.JBASiteID = S.JBSSiteID order by JBSRegion, JBAID desc" 
searchresults_cmd.Prepared = true

Set searchresults = searchresults_cmd.Execute
If NOT searchresults.EOF Then

	With Response
		.write("<source>" & VbCrLf)
		.write("<publisher></publisher>" & VbCrLf)
		.write("<publisherurl></publisherurl>" & VbCrLf)

		While NOT searchresults.EOF
            .write("<job>" & VbCrLf)
            .write("<source><![CDATA[" & (arrSR(11,i))& "]]></source>" & VbCrLf)
            .write("<title><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(1,i)))& " - " & ApplyXMLFormatting(RemoveChar(arrSR(2,i)))& "]]></title>" & VbCrLf)
            .write("<date><![CDATA[" & (arrSR(8,i))& "]]></date>" & VbCrLf)
            .write("<referencenumber><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(0,i)))& "]]></referencenumber>" & VbCrLf)
            .write("<url><![CDATA[" & (arrSR(10,i))& "/detail.asp?ID=" & (arrSR(0,i))& "]]></url>" & VbCrLf)
            .write("<company><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(9,i)))& "]]></company>" & VbCrLf)
            .write("<city><![CDATA[" & (arrSR(2,i))& "]]></city>" & VbCrLf)
            .write("<country>UK</country>" & VbCrLf)
            .write("<description><![CDATA[" & ApplyXMLFormatting(RemoveChar(arrSR(5,i)))& "]]></description>" & VbCrLf)
            .write("<salary><![CDATA[" & ApplyXMLFormatting((arrSR(4,i)))& "]]></salary>" & VbCrLf)
            .write("<jobtype><![CDATA[" & (arrSR(6,i))& "]]></jobtype>" & VbCrLf)
            .write("<category><![CDATA[" & (arrSR(3,i))& "]]></category>" & VbCrLf)
            .write("<sponsored><![CDATA[" & VbCrLf)
            
            IF arrSR(7,i) = "Y" AND arrSR(13,i) = "Y" then 
            	.write("1" & VbCrLf)
            End IF
            
            .write("]]></sponsored>" & VbCrLf)
            .write("</job>" & VbCrLf)

			searchresults.movenext
		Wend
		.write("</source>" & VbCrLf)
	End With
End If

searchresults.Close() 'Clean Up
Set searchresults = Nothing 'Clean Up
Set searchresults_cmd = Nothing 'Clean Up

Function RemoveChar(strInput)
    Set reg = New RegExp
    With reg
        .Pattern = "[^a-zA-Z0-9.<>?/@,.:!""£$&()_+=;#' \r-]"
        .Global = True
        result = .Replace(strInput, "")
    End With 
    RemoveChar = result
End Function  

Function ApplyXMLFormatting(strInput)
  strInput = Replace(strInput,"&", "&amp;")
  strInput = Replace(strInput,"&amp;amp;", "&amp;")
  strInput = Replace(strInput,"&#163;","&pound;")
  strInput = Replace(strInput,"&amp;#163;","&pound;")
  strInput = Replace(strInput,"&#169;","&copy;")
  strInput = Replace(strInput,"&amp;#169;","&copy;")  
  strInput = Replace(strInput,"'", "&apos;")
  strInput = Replace(strInput,"""", "&quot;")
  strInput = Replace(strInput, ">", "&gt;")
  strInput = Replace(strInput,"<","&lt;")
  ApplyXMLFormatting = strInput
End Function   
%>

Open in new window

0
 
garethtnashAuthor Commented:
Thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now