Solved

dBase System Resource Error

Posted on 2000-03-31
18
840 Views
Last Modified: 2013-11-24
Hi all

I am using the microsoft dBase driver in ODBC to access a .dbf file through ASP.

I am running NTserver with IIS.

It works great for a while and then after some recordset activity (not much either, say 15-20 collections of not much data) it comes back with this error.

<error>
Microsoft OLE DB Provider for ODBC Drivers error '8007000e'

[Microsoft][ODBC dBase Driver] System resource exceeded.
</error>

I then need to reboot my server, to get things running again. Not good. :(

I have checked to make sure that I have closed off my connections after using them.

I have upgraded to MDAC 2.5. I have installed the most recent Borland Database Engine.

I believe it to be a connection pooling issue or such???? perhaps. If it is how do I go about fixing it.??

There was a previous question of this nature, but the answer involved a reinstall of the OS etc. That is not an option for me.

Could this be a known bug or something like that, with the dBase ODBC drivers??

I am in deep trouble here if I can't work out what is going on really fast.

Thanks
Aaron.
0
Comment
Question by:abd051697
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 15

Expert Comment

by:robbert
ID: 2675734
Do you close and null the connection and recordset objects?

Set con = Server.CreateObject("ADODB.Connection")
Set rec = Server.CreateObject("ADODB.Recordset")
con.Open ...
rec.Open ...
rec.Close
con.Close
Set rec = Nothing
Set con = Nothing
0
 
LVL 8

Expert Comment

by:drittich
ID: 2675937
I would think robbert is on the right track here.  

1. Also examine any loops you are using (Do...Loop, For...Next, etc.), and look for possible runaway situations where variables are getting instantiated repeatedly, or other resource-using activites could occur.  

2. Can you post your code?  What is in your global.asa, this could play a large role.  

3. Are you using any include files that are repeating actions that they shouldn't?

4. I am doubting the conection pooling theory at the moment.  You are using too small a number of connections before it crashes for this to matter.

5. Can you duplicate your code using another ODBC driver to verify that you have not found an ODBC bug?  Import the dbf into Access and try the Jet oDBC driver with the same code, just modify your connection string.

Keep us posted.  I am around much of the day, and robbert owns this place!
0
 

Author Comment

by:abd051697
ID: 2676822
Thanks guys for your assistance.

Here is my code, very simple. Yet causing so much drama.

<--code-->
'get dBase data
Set oConndBase = Server.CreateObject("ADODB.Connection")

Set oCmddBase = Server.CreateObject("ADODB.Command")
                  
oConndBase.Open "DSN=00001033_dBase;UID=;PWD="

Set oCmddBase.ActiveConnection = oConndBase
Set oRsdBase = Server.CreateObject("ADODB.Recordset")

strSQL = " SELECT Hour(CDate([Sdate])) AS HVal, DateValue(CDate([Sdate])) AS DVal, Hr_wghts.C2TPH, Hr_wghts.C10TPH, IIf(CLng([C2TPH])<>0 or CLng([C10TPH])<>0,CLng([C2TPH])/(CLng([C2TPH])+CLng([C10TPH])),0) AS Rrecov FROM Hr_wghts WHERE (((CDate([SDATE])) Between #" & dBaseStartDate & "# And #" & dBaseFinishDate &"#)) ORDER BY CDate([SDATE]) "

'Response.Write "dbase= " & strsql & "<bR>"

oRsdBase.Open strSQL, oConndBase, adOpenStatic, adLockReadOnly, adCmdText

If ( oRsdBase.BOF And oRsdBase.EOF ) Then
    CountEmpty = CountEmpty + 1
    dBaseEmpty = true
'Response.Write "No dBase Data "
Else
    AlldBaseData = oRsdBase.GetRows            
End If

oRsdBase.close
Set oRsdBase = Nothing
      
oConndBase.close
Set oConndBase = Nothing      
<--end code-->


Robbert as you can see I close them all off ok.

Could it be my cursor types?? or the fact that I'm using Access style SELECT statements direct to the DBF??

I am only ever doing this once per page load.

I then use the values in the array to do everything. This takes the load off the recordset.

Drittich in response to your points.

1) I don't have any loops in this part of the code. Just the IF statement.
2) Code is as above, and I am not using a global.asa for this project.
3) No include files.
4) This is good know that it might not be a connection pooling issue.
5) I imported the dbf into Access and then used the MSAccess driver to get the data. It worked perfectly over about 40 attempts. With the DBF driver I would normally only reach 20 atttemps.

To get around this problem, could I automate an import of the DBF file into an ACCESS one, and then just communicate with Access??

I would hope that it doesn't come to this but it might have to. I have one day left to come up with something.

Thanks again guys.
Aaron.
0
 
LVL 15

Expert Comment

by:robbert
ID: 2676844
dBase shouldn't be scoping with too many connections.
0
 

Author Comment

by:abd051697
ID: 2676862
Robbert

I don't understand. scoping?

Thanks
0
 
LVL 15

Expert Comment

by:robbert
ID: 2676899
Sorry, this post was after yours. - I meant: dBase shouldn't be able to handle many connections.

But, in your case, it's the GetRows() statement, as I assume.

Try instead:

RecCount = oRsdBase.RecordCount

(or just do nothing after opening the recordset!) to make sure that the problem isn't because of this method.

----------------------------------------

BTW, you're using:

, adOpenStatic, adLockReadOnly, adCmdText

- This isn't known to ASP - and will be evaluated as ", 0, 0, 0"

Use:

, 3, 1, &H0001

instead or, include:

<!--#include file="adovbs.inc"-->
0
 

Author Comment

by:abd051697
ID: 2677378
Robbert

I have progressed this issue further, as per your last comments.

I quite simply put the page with a meta tag of refresh every 20secs so that I didn't have to sit there and keep probing it.

Using this method I was able to work out the GetRows method is not the culprit.

Even with doing nothing, just the opening of the record set and then closing it. I still get the system resource error after about 20-30 times.

It's looking bad for me isn't it.

Do you have any other ideas?

Thanks for your assistance so far.
0
 
LVL 15

Expert Comment

by:robbert
ID: 2677459
ADODB.Command doesn't provide a method "Open()".
Change:

Set oCmddBase = Server.CreateObject("ADODB.Command")

-to-

Set oCmddBase = Server.CreateObject("ADODB.Recordset")
0
 
LVL 15

Expert Comment

by:robbert
ID: 2677502
Sorry, the last comment was dumb.

- I tested your code (other SQL statement / database) with a dBase 5 database (exported from Access).
Refreshing in very short periods worked.
When the GetRows() method was not outcommented, sometimes the error occured:
The database engine could not lock table 'customer' because it is already in use by another person or process.
But, that's OK.

- Your SQL statement is strange, is it a dBase-specific SQL dialect? Maybe, try a simple SQL statement, first.

- Did you try drittich's proposal of porting the database to Access - to see if it's an dBase / ODBC or, ADO, issue?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 8

Expert Comment

by:drittich
ID: 2677640
If you are in a hurry I would go ahead with the conversion to Access - it has been used extensively with ADO/ODBC and the problems are well-documented here and elsewhere.  

You don't need to automate an import.  You can create an Access database where the tables are linked to the Excel spreadsheet.  So updates in the spreadsheet are automatically refelcted in the database.  This is a very inefficient way to construct a database, but if it solves your problem for now, go ahead with it and work out the details later.
0
 

Author Comment

by:abd051697
ID: 2678138
Gentleman

Thank you again for your responses.

After even more work. These are my findings.

Robbert, I tried a simple SELECT * FROM statement and it still errored out. So it's not the statement. That's about it for things to test. It must just be the driver or something wrong with my software install. I doubt it because I can run this on another NTserver with the same error occuring.

I did try Drittich's comments about Importing into Access. This works like a charm, but causes me a lot of grief to set this up.

Drittich. Linking to the DBF from Access still causes the error. I linked with DbaseIII format. Just a comment. You mentioned Excel spreadsheet?? I tried to import the DBF as XLS as well but it wouldn't let me.

So if I want to use Access I have to Import it and not link it.

Note: from Robbert's method of exporting a DBF from Access. I imported the DBF into Access, then exported it as a dBaseIII file again.

Tests on this newly generated file yield the same result. Error. At least it's consistant.

Looks like I can't crack this nut.

Unless you guys have anymore ideas, do you know how to automate an import into Access????

Thanks again so far, I really appreciate your comments, it's at least led me down the right path to knowing there is nothing I can do.
0
 
LVL 8

Expert Comment

by:drittich
ID: 2678169
Sorry, I was losing my mind when I said Excel.

You *can* link to a .dbf file.  No problem.  Try it and see.  That should be no hassle at all to setup.
0
 

Author Comment

by:abd051697
ID: 2678182
Drittich

Some confusion here.

I was successful in linking the DBF, which would have been great, but I still get the same system resource error.

I think Access must internally use ODBC to talk to the DBF. Otherwise there is no other way that it could still be getting the same error, even after changing my ODBC to be for Access.

In summary. I've linked the DBF into Access, and changed my ODBC connection to an Access one. Same Error.

Any thoughts?
0
 
LVL 8

Expert Comment

by:drittich
ID: 2678229
Hmm, that makes sense.  Of course it uses ODBC, what was I thinking?

Ok, I am with the program now.  

What version of IIS are you running, and what Service Pack level is the NT server at?  
0
 

Accepted Solution

by:
aoasalam earned 500 total points
ID: 2678855
You may follow my corrections below:



<--code-->
'get dBase data
Set oConndBase = Server.CreateObject("ADODB.Connection")
Set oCmddBase = "DSN=00001033_dBase;UID=;PWD="
Set oRsdBase = Server.CreateObject("ADODB.Recordset")
If ( oRsdBase.BOF And oRsdBase.EOF ) Then
 CountEmpty = CountEmpty + 1
 dBaseEmpty = true
 Response.Write "No dBase Data "
Else
strSQL = " SELECT Hour(CDate([Sdate]))
 AS HVal, DateValue(CDate([Sdate])) AS DVal, Hr_wghts.C2TPH,
                    Hr_wghts.C10TPH, If(CLng([C2TPH])<>0 or
               CLng([C10TPH])<>0,CLng([C2TPH])/(CLng([C2TPH])+CLng([C10TPH])),0) AS Rrecov FROM Hr_wghts WHERE
                    (((CDate([SDATE])) Between #" & dBaseStartDate & "# And #" & dBaseFinishDate &"#)) ORDER BY CDate([SDATE])"
oRsdBase.Open strSQL, oConndBase
End If
oRsdBase.close
Set oRsdBase = Nothing
oConndBase.close
Set oConndBase = Nothing
  <--end code-->
0
 

Author Comment

by:abd051697
ID: 2678895
Aoasalam

Thank you for your response. It has filled me with renewed hope.

However I'm having trouble implementing your solution.

I get this error relating to the missing Command object that was in my previous code.

Microsoft VBScript runtime error '800a01a8'

Object required: '[string: "DSN=00001033_dBase;U"]'

/plant/dbase.asp, line 131

Any ideas??
0
 

Author Comment

by:abd051697
ID: 2681737
Hi all

Some additional information.

I have found an article in a Citect (SCADA package) help forum. This is an exerpt from one help article.

<snip>
We have also found that a large number of ODBC drivers also leak memory every time you open a connection. That is every time you disconnect from the database the ODBC driver does not free all the memory it allocated when it connected. The result is that after connecting and disconnecting many times you can cause the performance of your computer to degrade and may even crash.
</snip>

In my case then this is what could be causing the problem and along the lines of what Steve has commented about.

In an effort to get around this problem. I have created an Access database that has the DBF table linked. This Access database then every 5mins updates an Access table which is a replica of the DBF table.

Doing ADO queries on that is 100% ok.

The only drawback is that Access needs to be continually running on the machine. Oh well. Better than nothing.

I will leave this question open for now as I still need a solution.

I will look to posting this into other forums.

Thank you all for all your assistance with this issue.

Regards
Aaron
0
 
LVL 15

Expert Comment

by:robbert
ID: 2681902
Wasn't dBase III a DBMS in the early 90's? At that time noone thought of multiple connections, or even needed them...

If the reason that you use it is a special SQL dialect, we could translate the statements easily to the Access dialect.

If you're not using Access because you get dBase data from elsewhere, regularly, we could write a simplest import script.

If you think, Access had to be installed on the server - only the driver has to be installed and, that came with MSDAC / NT Option Pack.

But, if you love it as others their oldtimer car, enjoy! :-)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Use System DSN 6 71
cron.exe SCOM alert 3 45
how to hide hopscotch tour popup page reload? 2 51
ASP Focus problem 3 30
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

10 Experts available now in Live!

Get 1:1 Help Now