?
Solved

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

Posted on 2011-05-12
5
Medium Priority
?
1,129 Views
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"?>
<%
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

0
Comment
Question by:garethtnash
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35745089
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35745103
0
 

Author Comment

by:garethtnash
ID: 35745116
the C drive was almost full, the database server runs on the C drive, could that be the issue?
thx
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35745120
yes , might be that cause this problem
0
 

Author Closing Comment

by:garethtnash
ID: 35745145
thank you
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 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