Access Error: Not enough space on temporary disk 1

[reporting]

Hello all. Haven't come across this one before. Database is Access 2K & using ODBC. Database is about 9 MB in size. OS is Windows XP and I am using CR 8.5.

I was at my client running a rather complex report I had designed. Just as the report was finally about to come onto the screen (8 mins), I rec'd the following error:

Crystal Reports: Database Error
ODBC error: [Microsoft][ODBC Microsoft Access Driver] Not enough space on temporary disk.

When I clicked on OK, I rec'd the "Error detected by database DLL" messsage and it stopped.

This was kind of bizarre as the report worked 15 minutes earlier. All I had done was add in some record selection logic so that, instead of choosing a begin date and an end date, the user could chooose the year & the month of the report.

Took a look at the machine I was using and it had only a couple hundred MB free. The machine was slow also (PIII 600 with 512 MB of DRAM) so I asked my client if he could upgrade the machine while I went back to the office with the data & reports.

Imagine my surprise when I got the same error on my CR dev machine! It's a 1.8 GHz P4 with 512 MB of DRAM, running Windows 2K. And it has 3 GB free on the system drive!!! So it's not lack of HDD space on my computer system...

Now the change that I made is not particularly complex. And it's something I have done many times before without any problems at all...

I took a look at CR Support and could find nothing about this error. Same thing on MS Support. The search term "odbc error microsoft access not enough space on temporary disk" produced 3500 hits at Google! Went through a bunch of them and found most of them appeared to be error messages produced by the Google Web Crawler.

So I refined my search, making it all of the words "odbc error microsoft access" and exactly the words "not enough space on temporary disk". That gave a couple of hundred hits, all of which were Google Web Crawler issues!

Going back to my report and seeing if there is another way to do what the client wants. Meanwhile, any ideas on this error message?

Thanks very much,
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[synapsevampire]

What probably occured is that by changing the record selection criteria, you forced Crystal to return all of the rows rather than have Access filter the rows.

Record selection criteria is tricky stuff, verify whether you're passing the SQL under database->show sql query. The where clause should reflect the criteria you enter.

Since you didn't share your criteria, I can't help there, but you may have to allow the users to select the YEAR and MONTH, and then in 2 formulas generate the appropriate start and end dates, and then reference those formulas in your record selection criteria.

-k

 

[reporting]

TNX SynapseVampire. You're right, there is no WHERE clause.

As to your comments, that's exactly what I did, in various ways. And, as you can see below, there's a reason why I didn't pass the record selection criteria along: it's a doozy!!!


// numbervar BeginMonth;
// numbervar NumberOfMonths;

// if {?Type of Report} = "YTD" then BeginMonth := 1 else BeginMonth := {?Report Month};
// if {?Type of Report} = "Monthly" then BeginMonth := {?Report Month};

// if {?Type of Report} = "YTD" then NumberOfMonths := {?Report Month} else NumberOfMonths := 1;

{WinInvoice.InvoiceDate} >= Date ({?Report Year}, {?Report Month}, 1) and
{WinInvoice.InvoiceDate} < Date (DateAdd (&quot;m&quot;, 1, Date ({?Report Year}, {?Report Month}, 1))) and

{WinInvoice.WIKey} = {WinInvJobs.WIKey} AND
{WinInvDist.WIKey} = {WinInvJobs.WIKey} AND
{WinInvDist.WIJobTran} = {WinInvJobs.TranNo} AND
{ARTransaction.ABSJobN} = {WinInvJobs.Job} AND

// the original date selector:
// {WinInvoice.InvoiceDate} >= {?DateFrom} and
// {WinInvoice.InvoiceDate} <= {?DateTo} AND

// my first record select change was this one:
// year ({WinInvoice.InvoiceDate}) = {?Report Year} and
// month ({WinInvoice.InvoiceDate}) = {?Report Month} and

// my final record select change used the BeginMonth & NumberOfMonths variables
// in the DateAdd function that I moved up to the top

// there were a couple other variations I tried that killed it also...

({WinInvDist.SalesAcct} = 30000 OR {WinInvDist.SalesAcct} = 32000) AND

({ARTransaction.ProductCode} = 10 OR ({ARTransaction.ProductCode} >= 30 AND {ARTransaction.ProductCode} <= 530)) AND
({WinInvJobs.ProductCode} = 10 OR ({WinInvJobs.ProductCode} >= 30 AND {WinInvJobs.ProductCode} <= 530)) AND
({WinInvDist.ProductCode} = 10 OR ({WinInvDist.ProductCode} >= 30 AND {WinInvDist.ProductCode} <= 530)) AND

({Salesperson.SalespersonN} = 1 OR
{Salesperson.SalespersonN} = 3 OR
{Salesperson.SalespersonN} = 4 OR
{Salesperson.SalespersonN} = 5 OR
{Salesperson.SalespersonN} = 10 OR
{Salesperson.SalespersonN} = 13 OR
{Salesperson.SalespersonN} = 15 OR
{Salesperson.SalespersonN} = 17 OR
{Salesperson.SalespersonN} = 19 OR
{Salesperson.SalespersonN} = 24 OR
{Salesperson.SalespersonN} = 25) AND

{Customer.CustomerN} >= 1 AND
{Customer.CustomerN} <= 499998



I got called in to this place because the original report took 48 hours to run!!! Not really a prob as they are a patient bunch ... but no one could use their MRP while it was chugging away! So that was a problem.

The MRP uses a proprietary database w/o indexes. Found out from the ISV that wrote it that I could export it to Access. Reports took a couple of hours. Indexing the right fields and optimizing the report got report time down to 8 minutes.

Then I tried to optimize the record selection. All of a sudden, the numbers were no longer accurate... Gave up on that real quick! Because I used up 2 of the 4 days they have budgeted speeding up the report, I don't have time to optimize the record select the report came with.

So then I got to the next part of the assignment: change the report so they could have it run as a monthly or a YTD report. As you can see by my record select code up top, I tried a couple of different ways (there are others, but they are in other reports...) to change the record select.

I am now looking at having two reports: a YTD and a monthly. As soon as I take the record select from each one and combine them with an if {?Type of Report} = &quot;YTD&quot; then ... , I get that misleading error message again...

Thanks again for your help. But, unless someone comes up with a solution to this (I sent a query about it to CR's AnswersByEmail also ... but I figure it'll be a while before I get an answer back from them), it looks like I am going to end up with 2 reports.

Regards, John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[reporting]

A little bit more info on this situation. FYI, this is a Commission report that calcs how much to pay the sales reps... All my results have been checked against Feb 2003 (my client provided me with commission reports for that month).

While I had lunch, I decided to check and see what the results would be if I ran it for all of 2003. Imagine my surprise when came back from lunch to find that CR had the same &quot;Not enough space on temporary disk&quot; error! This on a report that worked just fine for Feb 2003!!!

Took a look at the SQL Query for the 2002.01 to 2002.12 report as well as the 2003.02 one. There is a WHERE clause in both of them. The SQL is rather complex (due, of course to the complex record select above..):


SELECT
WinInvJobs.`WIKey`, WinInvJobs.`TranNo`, WinInvJobs.`Job`, WinInvJobs.`ProductCode`,
WinInvoice.`WIKey`, WinInvoice.`CustomerN`, WinInvoice.`InvoiceDate`,
WinInvDist.`WIKey`, WinInvDist.`SalesAcct`, WinInvDist.`SalesAmt`, WinInvDist.`ProductCode`, WinInvDist.`WIJobTran`,
OpenJob.`JobDescription`,
ARTransaction.`ProductCode`, ARTransaction.`SalesmanN`, ARTransaction.`ABSJobN`,
Customer.`CustomerN`, Customer.`CustomerName`, Customer.`SalespersonN`,
Salesperson.`SalespersonN`, Salesperson.`Salesperson`
FROM
(((((`WinInvJobs` WinInvJobs INNER JOIN `WinInvDist` WinInvDist ON
WinInvJobs.`TranNo` = WinInvDist.`WIJobTran`)
INNER JOIN `WinInvoice` WinInvoice ON
WinInvJobs.`WIKey` = WinInvoice.`WIKey`)
LEFT OUTER JOIN `OpenJob` OpenJob ON
WinInvJobs.`Job` = OpenJob.`JobN`)
INNER JOIN `ARTransaction` ARTransaction ON
WinInvoice.`InvoiceN` = ARTransaction.`InvoiceN`)
INNER JOIN `Salesperson` Salesperson ON
ARTransaction.`SalesmanN` = Salesperson.`SalespersonN`)
INNER JOIN `Customer` Customer ON
ARTransaction.`CustomerN` = Customer.`CustomerN`
WHERE
WinInvoice.`InvoiceDate` >= {ts '2002-01-01 00:00:00.00'} AND
WinInvoice.`InvoiceDate` < {ts '2002-03-01 00:00:00.00'} AND
Customer.`CustomerN` >= 1 AND
Customer.`CustomerN` <= 499998 AND
(WinInvDist.`SalesAcct` = 30000 OR
WinInvDist.`SalesAcct` = 32000) AND
(ARTransaction.`ProductCode` = 10 OR
(ARTransaction.`ProductCode` >= 30 AND
ARTransaction.`ProductCode` <= 530)) AND
(WinInvJobs.`ProductCode` = 10 OR
(WinInvJobs.`ProductCode` >= 30 AND
WinInvJobs.`ProductCode` <= 530)) AND
(WinInvDist.`ProductCode` = 10 OR
(WinInvDist.`ProductCode` >= 30 AND
WinInvDist.`ProductCode` <= 530)) AND
(Salesperson.`SalespersonN` = 1 OR
Salesperson.`SalespersonN` = 3 OR
Salesperson.`SalespersonN` = 4 OR
Salesperson.`SalespersonN` = 5 OR
Salesperson.`SalespersonN` = 10 OR
Salesperson.`SalespersonN` = 13 OR
Salesperson.`SalespersonN` = 15 OR
Salesperson.`SalespersonN` = 17 OR
Salesperson.`SalespersonN` = 19 OR
Salesperson.`SalespersonN` = 24 OR
Salesperson.`SalespersonN` = 25)
ORDER BY
ARTransaction.`SalesmanN` ASC



The SQL for the 2002.01 to 2002.12 report is identical except for the first two lines of the WHERE clause:


WHERE
WinInvoice.`InvoiceDate` >= {ts '2002-01-01 00:00:00.00'} AND
WinInvoice.`InvoiceDate` < {ts '2003-01-01 00:00:00.00'} AND



I then decided to try CR's Direct Driver for Access rather than ODBC. 2.5 hrs later, CR says 0 of nearly 50,000,000 records read!!! This in a DB with 8 tables, with between 5,000 and 11,000 records per table... So this is obviously not the right path to take... The yearly report might take 48 hours to run again as that &quot;2.5 hrs later&quot; was a report for Feb 2003.

I am now starting to think that this misleading &quot;Not enough space on temporary disk&quot; error has something to do with the LOJ between OpenJob & WinInvJobs table. Or perhaps Access just cannot handle a query this complex without blowing up??? Not sure which... I know it has nothing to do with size: last week, I was doing reports on an Access 2000 DB that was nearly at the 2 GB Access limit (an extract from another clients SQL Server DB). But that DB didn't have the complex joins & record selection that this one has...

Any ideas out there?

Thanks very much,
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[reporting]

Looks like it's time to start pushing clients to move to CR 9!!!

After spending more time on this report yesterday (really should say wasting more time on it...) and not getting any further ahead, I moved it to my production machine (Win 2K Pro, 2.4 GHz, 512 MB DRAM, with both CR 8.5 & CR 9). Ran it first with CR 8.5. Same error message. Then ran it on CR 9.

The report ran to completion without problems. Not only did it run to completion, it was MUCH faster: it took only a couple of minutes to create a report for 2002.01 to 2002.12, while a monthly report for 2003.02 took 8 minutes using CR 8.5.

So that's the end of the line on this report!
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca