Solved

Dreaded "Cannot Open Any More Tables" OleDb provider .net 2.0

Posted on 2010-08-13
8
1,693 Views
Last Modified: 2013-12-25
Hello Experts,

Really out of ideas on this one...  I've got a web app that hosts many different Access databases for my clients, and serves up web reports from these databases.  Everything has been working fine for quite a while, until recently (a few months ago) I started getting this error.  The unique thing about this is, once this error hits, it hits for ALL attempted connections to ANY of the Access databases.  Meaning, this wasn't specific to one database...  I can look in the application log, and all of the sudden this error will show up, and it shows up site wide no matter what database is trying to be connected to.

At first, I thought it was my code.  I thought surely I'm not closing something out properly somewhere or taking care of closing DataReaders, Command objects, or Connections somehow (all OleDb).  So I scoured my code and made sure I was closing everything properly.  Still getting the error, I changed all of my usage of command objects...  I put them all in "using" blocks to help make sure they were getting disposed of.  Still no fix.  I even went so far as to close the command object right before the 'end using' statement.  I am also disposing of the OleDbDataReader object when done.  (I should note, I am using ALL DataReader objects, and there are no update or insert queries, ONLY SELECT queries).

So then I thought it was my poor server running out of memory under load (granted, it did only have 1gb).  So this week, I just got done upgrading to a new server with 3gb of memory (moved from Win2003/IIS6 to Win2008/IIS7).  Everything has been working fine for a few days now, and then bam, here comes the error again.  This error will continue to occur until I recycle processes for the app pool.  I have the app pool set to recycle at 4:00 AM every day.

Here is how I'm connecting to the database:

                  con = New OleDbConnection(provider & "Data Source=" & HttpContext.Current.Server.MapPath("\" & sfs.DatabasePath & sfs.DatabaseName))
            con.Open()

            initPageVariables()
            configPageHeader()

         ' process page and generate report here

            con.Close()
            con.Dispose()

Open in new window



I am running many different queries to generate the report, or process input from the user to determine what they want to see.  Here is how I run a typical query:

            sql = "SELECT * FROM MC WHERE EY={0} AND EID={1} AND SEID={2} AND CID={3}"
            sql = String.Format(sql, EY, EID, SEID, CID)
            Using com As New OleDbCommand(sql, con)
                Dim r As OleDbDataReader = com.ExecuteReader
                If r.Read Then
                    MID = r("MID")
                Else
                    MID = 0
                End If
                r.Close()
                com.Dispose()
            End Using

Open in new window


Other tidbits of information that may/may not be useful:

I've upgraded much of my web app to asp.net, but I do still have some clients using the classic asp pages.  I've noticed that classic asp and asp.net both trying to connect to the same database doesn't work out so good.  While I do have some clients still using the classic pages, and others using the asp.net pages, I don't intermingle the two technologies with the same database.  I think I should be OK with this approach, but maybe Jet 4.0 doesn't like this in some way?

Can't think of anything else weird at the moment.

I need to get this figured out, and I'm running out of ideas!  Will be happy to provide more code (if required) or answer any other questions about the setup.  But I am just deflated after being convinced this was a memory issue, and after all the work of moving my app to a new server, here it comes again...
0
Comment
Question by:Jahelka
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
What provider are you using?  If ODBC, then I would turn off connection pooling.
Aside from that, JET has an internal table ID limit of 2048 and when you hit that, that's the error you get.  Increasing memory doesn't help, as this is an interal limit within JET.
There are not many options.  The only thing one typically does is:
1. Reduce the number of concurrent operations (ie. closing of recordsets right away).
2. Reduce the number of nested queries.
3. If Access is being used, make sure to minimize the number of code module open (doesn't apply here).
What thows me is the pooling aspect; that you see it site wide.  That suggets the connection to JET is being shared site wide, but I don't understand that unless it's the ODBC provider.
JimD.
0
 
LVL 17

Assisted Solution

by:jrm213jrm213
jrm213jrm213 earned 50 total points
Comment Utility
Not sure if this will make any difference or if it is causing a problem or not, but the "using statement" automatically disposes of itself, so calling dispose on the object that is used in the using statement probably isn't needed and there is a slim chance that disposing that object inside the using statement is causing it to remain open at a lower level, or at least causing the oledb connection object to think the connection wasn't closed. If you are worried about it not getting closed by the using, go ahead and dispose it right after the End Using statement.

Shot in the dark, but sometimes they hit.

0
 
LVL 1

Author Comment

by:Jahelka
Comment Utility
JimD:

I'm using OleDb not ODBC.  Didn't I make that clear?  =)  J/K.  I guess my question is, does the 2048 limit apply to a single connection?  Or does it apply to the Jet Engine itself?  In other words, is it one database (or one connection) that is limited to 2048, or ALL CONNECTIONS COMBINED that are limited to 2048?  If it is the latter, could my using classic asp and asp.net at the same time be causing the problem?  Also, if it is the latter, I could definitely see that under any kind of load, I could probably very easily be exceeding this limit SITE WIDE, but I can't imagine exceeding that per database.

jrm:

I actually added that com.dispose in AFTER I was having problems thinking that maybe that would force it to dispose, rather than waiting on GC.  I'll have to go check, but wouldn't com be out of scope after the End Using line?  If you think it is bad practice to explicitly dispose within the Using statement block, then I will take all of those out and try it.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<< Or does it apply to the Jet Engine itself? >>
Applies to the JET engine itself, so it would be all connections combined. This is what I didn't think through throughly before; in a web situation, only the server software is touching the DB, so there is only one instance of JET running, which all connections run through.
<<If it is the latter, could my using classic asp and asp.net at the same time be causing the problem?  >>
  No.  This limit exists and happens even when using the Access product on a desktop, although in that case it's much harder to hit as Access can only open one DB at a time through the UI (you can do it though).  But if your working in code, every database you open works against the limit.
  and you be surprised what counts towards that limit.  In your case, it's a little different in that your working with straight data, but every table you open and every field (a field reference contains a table ID) counts towards that limit.
Jim.
 
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:Jahelka
Comment Utility
Jim, this is GREAT stuff.  Now knowing some of the stuff you have just told me, makes me realize my worst fear.  Time to move away from Access.  That means changing code, not only on the web server, but on the windows app that my clients use to populate the database.  Lots of work ahead...

So just to run through a few more ideas...  I assume that splitting the website up into chunks, and putting say, half the website on one AppPool, and the other half on a different AppPool does me no good, it is still one instance of Jet running?

I think there are some changes I could make to my queries to lighten the load.  You mentioned something I didn't quite fully understand...  Each FIELD that I request counts against me!?!  Does that mean that I should request ONLY the fields I need for reporting rather than using * whenever I can?  Does that make any difference in the count against me?

I picked a really good example of bad coding technique in my query I listed above.  That same query that returns a whole DataReader (even though it should never have more than one record) could be re-written as r.ExecuteScalar as I only need one value from one record.  I don't think I do that anywhere else on the page, but I can go back and check.  Will that help me out in my count?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
Comment Utility
<<So just to run through a few more ideas...  I assume that splitting the website up into chunks, and putting say, half the website on one AppPool, and the other half on a different AppPool does me no good, it is still one instance of Jet running?>>
 I'm not 100% sure on the answer to that.  I would think each application pool would have to get it's own JET instance because the pools are isolated from one another.
<<You mentioned something I didn't quite fully understand...  Each FIELD that I request counts against me!?!  Does that mean that I should request ONLY the fields I need for reporting rather than using * whenever I can?>>
  You betcha.  You should only be asking for fields that you need.  If your using * and the table has 60 fields, when the query runs (assuming only one table is in the query), that's 61 TableID's used up.  
<< Will that help me out in my count? >>
  I would think it would, but I'm guessing there as the internals of how JET processes things have never been fully documented.  I only know about the table ID limit because in JET 3.5, the limit was push up from 1024 to 2048.  It was with that they (Microsoft) spelled out what counts towards that limit.  
  But it wasn't even till latter on that everyone realized their was bit more to it then that even.  In a Access app, every code module opened also uses a table ID (Access/JET uses a table ID to fetch the code from a BLOB field in one of the system tables).
 Makes sense when you think about it, but Microsoft hadn't pointed it out at the time when they raised the limit.
JimD.
0
 
LVL 1

Author Closing Comment

by:Jahelka
Comment Utility
Thanks for the help guys.  I'm going to start another question to ask about possible solutions for either creating a web garden (increasing worker processes above 1) and/or somehow splitting things up into separate application pools somehow to aleviate my problems until which time I can switch to a new database back end (it's going to take a while).  I've been eyeing VistaDB for a while now.  Might be time to bite the bullet and download the demo and see how it works out for me.
0
 
LVL 1

Author Comment

by:Jahelka
Comment Utility
Started up a new question to further my learning here:

http://www.experts-exchange.com/Software/Server_Software/Web_Servers/Microsoft_IIS/Q_26402212.html

Hopefully I get a good solution there.  Just wanted to share for anyone else that wanted to continue on the topic at hand.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 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

12 Experts available now in Live!

Get 1:1 Help Now