Microsoft OLE DB Provider for SQL Server error '80040e14'

Hello Experts - Help please..

I'm trying to run a recordset that returns over 25,000 records and exports the data to xml.

However when i run the recordset I get the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'
Could not allocate space for object 'dbo.SORT temporary run storage:  140879561097216' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

/feeds/mnm/feed.asp, line 43

Any idea how to fix this please?

My recordset is below -

<%@LANGUAGE="VBSCRIPT"%><?xml version="1.0" encoding="ISO-8859-1"?>
<%
Server.ScriptTimeout=450
%>
<% Response.Buffer = true
   Response.ContentType = "text/xml"
   
Function ApplyXMLFormatting(strInput)
  strInput = Replace(strInput,"&", "&amp;")
  strInput = Replace(strInput,"'", "&apos;")
  strInput = Replace(strInput,"""", "&quot;")
  strInput = Replace(strInput, ">", "&gt;")
  strInput = Replace(strInput,"<","&lt;")
  strInput = Replace(strInput,"&amp;amp;", "&amp;")
  strInput = Replace(strInput,"&amp;#163;","&pound;")

  
  ApplyXMLFormatting = strInput
End Function   
    %>
    <% 
Function ApplyANDFormatting(strInput)
  strInput = Replace(strInput,"&", "")
  strInput = Replace(strInput,"'", "")
  strInput = Replace(strInput,"(", "")
  strInput = Replace(strInput,")", "")
  strInput = Replace(strInput,"-", "")
  ApplyANDFormatting = strInput
End Function   
    %>
<!--#include file="../../Connections/recruta2.asp" -->
<!--#include file="../../Connections/removeutfformatting.asp" -->
<%
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    WHERE JBSSIteOwnerID  <> 3    AND JBADatePosted >= DATEADD(d,-JBAPostFor,GETDATE())  ORDER BY JBSURL,JBAID DESC" 
searchresults_cmd.Prepared = true

Set searchresults = searchresults_cmd.Execute
searchresults_numRows = 0
%>
<source>
  <publisher></publisher> 
  <publisherurl></publisherurl> 

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
searchresults_numRows = searchresults_numRows + Repeat1__numRows
%>
<% 
While ((Repeat1__numRows <> 0) AND (NOT searchresults.EOF)) 
%>
<job>
<source><%=(searchresults.Fields.Item("JBSURLShort").Value)%></source>
<title><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBATitle").Value)%> - <%=ApplyXMLFormatting(searchresults.Fields.Item("JBAPayRate").Value)%> - <%=ApplyXMLFormatting(searchresults.Fields.Item("JBALocation").Value)%>]]></title>
<date><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBADatePosted").Value)%>]]></date>
<referencenumber><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBAID").Value)%>]]></referencenumber>
<url><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBSURL").Value)%>/detail.asp?ID=<%=(searchresults.Fields.Item("JBAID").Value)%>]]></url>
<company><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBCLName").Value)%>]]></company>
<city><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBALocation").Value)%>]]></city>
<country><![CDATA[UK]]></country>
<description><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBADescription").Value)%>]]></description>
<salary><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBAPayRate").Value)%>]]></salary>
<jobtype><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBAEmplymentType").Value)%>]]></jobtype>
<category><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBACategory").Value)%>]]></category>
<sponsored><![CDATA[<%IF searchresults.Fields.Item("JBAFeaturedJob").Value = "Y" AND searchresults.Fields.Item("sponsored").Value = "Y" then%>1<%End IF%>]]></sponsored>
</job>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  searchresults.MoveNext()
   if Repeat1__numRows mod 10 = 0 then 
      Response.Flush() 
 end if
Wend
%>
</source>
<%
searchresults.Close()
Set searchresults = Nothing
%>

Open in new window

garethtnashAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
yes , might be that cause this problem
0
 
Pratima PharandeCommented:
This error happens for one of two reasons. Either the disk where the data is stored is full, or the database is not set to auto-grow and it has reached capacity. If the former, you will need to free up space on the drive (or move the data files to a different location). If the latter, you will need to set the database to auto-grow, or clear out stale data and perform a shrink.

refer

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html
0
 
Pratima PharandeCommented:
0
 
garethtnashAuthor Commented:
the C drive was almost full, the database server runs on the C drive, could that be the issue?
thx
0
 
garethtnashAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.