ODBC Connections

at least i think...

[Scally_Ally]

Hi All,

I am in charge of a server remotely using the remote desktop.

it seems that when the server gets a medium to large amount of traffic the connections to the database seem to break. I am using ODBC to connect to the database and by way of trial and error i have narrowed down that it must be this as other parts of the site that do not connect to the database via ODBC work alright still.

The error i get is an unspecified error at the first site of any database activity.

My question is this, is there any way to optimise the ODBC connections to deal a larger volume of traffic?

Thanks

Ally

[mattglet]

This isn't really a hardware question, more of a development question.

When you open a connection to a database in your code, you should be closing it when you are done with it. This practice will ensure that your connection pool will contain only the connections needed to operate your site, and consume only the needed resources as well.

[Scally_Ally]

Thats the wierd thing...

I have checked through all of my code to make sure that i had closed all database connections and sessions that are not in use at that particular time.

the error i get is

provider error '80004005'
unspecified error

this seems a little vague to me, it is a new iis server i am using but when the site was on the old server it worked perfectly well.

I am thinking that maybe it some kind of setting that maybe i have overlooked.

Thanks

[mattglet]

Can you post the line of code causing the error?

[Scally_Ally]

conn.open "DSN=myDatabase;"

this is where the page falls over and the server goes wrong, on the page this is the first place any database work is started.

when i get the error report from the server it says that the connection had timed out, so i will assume that that means that it couldnt create a connection within the alloted time which is 30 seconds (at least i think).

In order to over ride this problem i have to restart the server.

Im really lost with this, it seems to happen about once every 2 days or so.

Thanks

[mattur]

Assuming you're using ASP, one way to optimise ODBC connections is to not use them(!).

ASP uses ADO which uses OLEDB as the data API. When you use an ODBC connection in ASP you're actually adding an extra layer to the data stack - the OLEDB to ODBC conversion.

So using ODBC in ASP is like:
ASP -> ADO - > OLEDB -> ODBC -> database

You can optimise this by using an OLEDB connection instead:
ASP -> ADO - > OLEDB -> database

This is especially important when using Access as the database (are you using Access?)

For more information google for oledb connection strings.