A friend calls me earlier today(and wakes me up), with a problem on a system that he is maintaining.
since the customer needed the system ASAP, he asked me to deal with it .

So, what is the problem ?

getting in to the system, wins the happy user with this error message :

8007000e System resource exceeded
Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Microsoft Access Driver] System resource exceeded.

needless to say that is an "ASP-Access" system.

lets review all our facts :

• The system written in ASP
• The system's database is Access
• It deployed on a shared hosting server
• the system sends some error message - probably DB related

after googling a while, got the answer that the problem is connection pooling related.
this could mean one of two things :

1. There are places in the system which the connection not closed properly, which overloads the connection pool.
2. There are too many simultaneous users, which cousing too many connections at the same time.

Since there is only one user on the system, the second option is probably not the answer.

so, how do we clear the connection pool on a shared hosting server ???

lets see what are the options (or to be exact : what are our limits on shared hosting on that issue ?)

• We can't access the IIS configurations.
• We can't get a remote desktop connection (is it too much to ask ?)
• We don't have an access to the connection pool configurations
• We cant restart the server.

Here is the solution :

1. Access to the system to get the error.
2. rename the DB from db.mdb to db1.mdb
3. run the system again (this is an important step),
   the system will think that it's missing it's database and recycle the connection pool(mission accomplished)
4. rename the database file to the original name (db.mdb)
5. run the system again.

now, i have to say , that this is a problem solver for the short run,
whatever caused the problem will probably cause it again at some point.
so, the best solution , is to do some harsh code review and find the problem (where the connection is opened but not closed...)

take care.

remember this neat syntax that exists in oracle database for hierarchy selects ?
actually , its pretty simple :

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
STARTWITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

ever tried to do the same in sql server ?
well, these kind of syntax just does not exist,
we need to work very hard to create such a feature in our database.

so, what do we have:

• a table that contains entities
• each entity connected to some father entity
• each connection describes a "father - son" relation between the two entities

lets not forget the things we need to relay on , when implementing :

• what will happen when we will delete the father of some sub tree ?
• what should we do when we update/add a record ?
• how will we select the data ?
• algorithm efficiency is crucial, if we will need to wait 5 minutes for the data, it's not worth it

the first (but apparently the worst) idea that came to my mind is recursion
lets look at this table :

we have we that the employee shimon is the "big boss" (because there is no other boss above him),
under shimon we have the employees Gaby and yossi,
and under Gaby, we have another 2 employees : Koby and jack

the recursive solution is to write some stored procedure that will receive the employeeID and return as a data-Table the results
i will not add the code for this solution and surely will not recommend it because it was many problems :

• for each record we received as a descendant ,
  we need to run with the function and get her descendants,
  and so on, until there are no descendants for the node
• we are limited to 32 levels of hierarchy
• the runtime will depend on the row count that is in the table (we will need to run on each of the rows one at the time)
• the run on the node will look like this :
  
  
  

by the way, the most common way that I've seen to select hierarchical structure,
is simply by setting a join between the levels in the select query.
for example :

SELECT TopBoss.Name TopBoss, Boss.Name Boss, Employees.Name Employee
FROM Employees
INNERJOIN Employees AS Boss ON Employees.BossID=Boss.EmployeeID
INNERJOIN Employees TopBoss ON Boss.BossID=TopBoss.EmployeeID

this apply to the selection of three levels

For each level, you'd need to join the table to itself...not an attractive option if you have 5 or more levels ,
you don't know how many levels you will have to select, there is no way can control it!
It would be great if it could join itself as many times as needed. This is called a recursive join, and though some database products support it (Oracle has the CONNECT BY syntax) SQL Server is not one of them.

the other way is based on a thread that i read here about hierarchies,

lets create a table :

CREATE TABLE Tree (
Node intNOTNULLIDENTITY(100, 1),
ParentNode int,
EmployeeID intNOTNULL,
Depth tinyint,
Lineage varchar(255) )

the extra fields that has been added are the "lineage" and the "depth"

• Depth - for saving the current depth of the record in the hierarchy
• Lineage - for saving all the ancestors of the record as a concatenated string

after filling the needed data for relations, the table looks like this :

Node	ParentNode	EmployeeID	Depth	Lineage
100	NULL	1001	NULL	NULL
101	100	1002	NULL	NULL
102	101	1003	NULL	NULL
103	102	1004	NULL	NULL
104	102	1005	NULL	NULL
105	102	1006	NULL	NULL

The next part is to find the root node of the tree, also known as the top-level, etc.
That's the node that has no parent (Null), so we will start there and set the Lineage column as the root:

UPDATE Tree SET Lineage='/', Depth=0 WHERE ParentNode IsNull

Once we did that,
we can then update the rows who are the descendant of the root node:

WHILE EXISTS (SELECT * FROM Tree WHERE Depth IsNull) 
   UPDATE T SET T.depth = P.Depth + 1, 
   T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
   FROM Tree AS T 
   INNERJOIN Tree AS P ON (T.ParentNode=P.Node) 
   WHERE P.Depth>=0 
   AND P.Lineage IsNotNull 
   AND T.Depth IsNull

this loop will run once for each level of the hierarchy (not for each node as the recursion method.)
so, with data representation of 10,000 records with 8 levels of hierarchy,
this code will run only 8 times to populate the needed data of the "lineage" field and the "depth" field, and this "heavy" procedure will happen only once at the setup.
the table should look like this after the given operation :

You'll notice that for each node, the entire lineage back to the root is stored. This means that finding someone's boss, or their boss' boss, doesn't require any self-joins or recursion to create an indented list. In fact, it can be accomplished with a single SELECT.

SELECT Space(T.Depth*2) + E.Name AS Name
FROM Employees E
INNERJOIN Tree T ON E.EmployeeID=T.EmployeeID
ORDERBY T.Lineage + Ltrim(Str(T.Node,6,0))

maintaining the table is really not a big deal if we will use triggers.
think about the new inserted record as the row that has not been filled in the setup process.
so the insert trigger should be :

UPDATE T SET T.depth = P.Depth + 1,
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/'
FROM Tree AS T
INNERJOIN Tree AS P ON (T.ParentNode=P.Node)
WHERE P.Depth>=0
AND P.Lineage IsNotNull
AND T.Depth IsNull

the update trigger should do pretty much the same : building the 2 extra field all over again.

suggestions and request will be repplied :)