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

Posted on 2011-05-12
Last Modified: 2012-05-11
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"?>
<% 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

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)) 
<title><![CDATA[<%=ApplyXMLFormatting(searchresults.Fields.Item("JBATitle").Value)%> - <%=ApplyXMLFormatting(searchresults.Fields.Item("JBAPayRate").Value)%> - <%=ApplyXMLFormatting(searchresults.Fields.Item("JBALocation").Value)%>]]></title>
<sponsored><![CDATA[<%IF searchresults.Fields.Item("JBAFeaturedJob").Value = "Y" AND searchresults.Fields.Item("sponsored").Value = "Y" then%>1<%End IF%>]]></sponsored>
   if Repeat1__numRows mod 10 = 0 then 
 end if
Set searchresults = Nothing

Open in new window

Question by:garethtnash
    LVL 39

    Expert Comment

    by:Pratima Pharande
    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.

    LVL 39

    Expert Comment

    by:Pratima Pharande

    Author Comment

    the C drive was almost full, the database server runs on the C drive, could that be the issue?
    LVL 39

    Accepted Solution

    yes , might be that cause this problem

    Author Closing Comment

    thank you

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now