10/17/2003 NT26 and ASP pages

[newmem]

These problems are not coming in any win server other than nt26, are they? then why the need to make changes in the scripts?

Has anyone tested the Jet connection scripts on nt27?

If you remove jet then everyone will have to be asked to make the change, where most people use pre-built scripts and do not even have a programmer to go for support, so I think it's better to move back to win2000 rather than asking so many people to make a change.....if everything was working fine in win2000 till now then why move to trouble making win2003?

just my thoughts on this....

[antic]

Originally posted by admin
I think if we can get every one to use this:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\mydb.mdb;Uid=admin;Pwd="

It should not use jet and should help with the issue.. [/b]

Actually it's the other way around - people need to use the OLEDB connection method:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\myDb.mdb"

It seems there are two things that we need to sort out for now:
1. How many people are using ODBC on the 2003 servers, and
2. Whether it is ODBC or Jet itself which is causing the problem with IIS6.

The first one should be easy, as I assume all you have to do is go into the Control Panel on the server and count the number of System ODBC entries. This will at least tell you if it's feasable, time-wise, to take those related sites and copy them over to a W2K server.

The second one may be solved easily the following way:
1. Set up a 2003 server machine which is not being used by anyone,
2. Set up an Access ODBC application in the default web site (maybe use Snitz Forums in ODBC mode)
3. Download and set up MS's Web Application Stress Tester and use it to pummel the web application with requests,
4. See what happens.

If it falls over, then re-install Snitz Forums in OLEDB mode and do the same thing.

If it falls over again, then I guess it's Jet that's the problem, which means changing over to OLEDB won't achieve a thing.

Just an idea. Of course Microsoft should be doing some testing like this right now... maybe you can pass this suggestion to them.

[Brangwyn]

Ok I'm confused, the post started off saying it was JET that is the problem, in which case the Connection String Brad gave is correct. However in later posts MS are saying use JET OLEDB implying that JET isn't the problem (any half decent developer already uses OLEDB anyway becuase ODBC is basically crap).

Which is it Brad ? ODBC or JET thats the problem (or believed to be the problem)

count the number of System ODBC entries

Would be missleading if most folks use DSN-Less connections though.

[antic]

... the post started off saying it was JET that is the problem, in which case the Connection String Brad gave is correct.

I think I see what you mean... My understanding is that "Jet" is the layer that lets us use Access databases, and that there are two ways of interfacing with Jet - ODBC and OLEDB.

This may be incorrect on my part, but in my mind you can't do without "Jet" as such if you want to use Access. You can only rid yourself of the ODBC layer that interfaces with Jet.

So (in my mind), no matter what the connection string is, Jet itself will always be in use if you're connecting to an Access datbaase.

The OLEDB method is preferable because of problems with the ODBC layer, but both use Jet.

Delete this post if I'm wrong as it would be far too emabrassing.

Addendum:
MS is saying the preferred method of connecting to Access is via OLEDB, but they also say don't use Access for web apps, which I guess is the same as saying don't use Jet.

Addendum 2:
Be that as it may, if it crashes IIS or Windows, then it's a major problem and they should fix it. They're quick to say "we don't recommend it" but I'd like to see the community's reaction if they prevented Access being used with IIS entirely. Access is certainly not up to the task for busy sites, but at worst it should slow the site down not crash it.

[Brangwyn]

Well thats kinda the way I understand it too though its something I regularly trip up on as well.

JET = Access Database Engine
JET.OLEDB = The Native OLE DB provider for Access

Microsoft Access Driver (*.mdb) = ODBC and does not require JET to be installed ?? part of MDAC ?? (though MDAC used to include JET anyway, so I could be completely wrong).

IIRC OLE DB hasn't been around as long as access (I recall access versions going something like 1.0, 1.1, 2.0, 7.0, 97 ..... etc) so there must have been an ODBC driver prior to OLE DB which I can only assume was part of MDAC (I think MDAC 1.5 was what shipped with NT4 originally).

Had a quick google but can't find any articles really explaining well the completele relationship between all these drivers etc (and I'm too tired to scour technet/MSDN) so I can't back what I'm saying up at all, I really could be talking through a hole in my bum.

[mresell]

But if you only had iis6's fabulous new Worker Process Isolation feature you'd be home free! :grin:
I swear I would feel like taking a bath after talking to some of those MS people.
Gadgetgal99 that outlookexpress thing was part of what I was talking about. They integrated it w/ ie that they integrated w/ the os that breaks w/ a mismatched dll. Then they say we are taking it away so you have to use outlook or somethin else(my choice anyway). But you know how that "MONO" culture goes! Hey that sounds like a contagious disease.

[newmem]

Originally posted by antic
Addendum 2:
Be that as it may, if it crashes IIS or Windows, then it's a major problem and they should fix it. They're quick to say "we don't recommend it" but I'd like to see the community's reaction if they prevented Access being used with IIS entirely. Access is certainly not up to the task for busy sites, but at worst it should slow the site down not crash it.

Perhaps this patch will solve the problem?

http://support.microsoft.com/?kbid=829558

Maybe someone should take a look at it if it's not already installed, and it is installed then perhaps it should be uninstalled, bcos many of my own problems with my PC get solved by uninstalling the new Windows patches which themselves need patching

[Brangwyn]

Already tried and failed as I understand it.

So we have upgraded it to SP8 to see if that will fix the issue

[newmem]

2 questions:

(1) Is this problem only in nt26 or in all win2003 servers including nt27?

(2) I checked one script that was developed a few months back to see what is the method that is used to connect to the access database, do let me know if this is the one that is being recommended to use or vice versa:

cnngo.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("./foldername/access.mdb"), "admin", ""

[Brangwyn]

Only NT26

The connection string you've got there is the best to use and as mentioned in the thread by MS it is what they seem to be reccomending.

[newmem]

Originally posted by Brangwyn
Only NT26

hope the problem won't come in all other win2003 servers too......unless the problem is located how can we prevent the same thing happening in all the 2003 servers?

also, I read in this thread that IIS 6 has something called Worker Process Isolation feature which will prevent IIS from crashing when one script fails. I think that's very great on a shared server where so many scripts are running at once, this would prevent downtimes. Are we using this new feature in the win2003 servers?

[antic]

I'm not sure how well the Worker Process Isolation setting works on busy servers - as I understand it, the less pooling you do, the more resources get used.

It would be worth a try, but if it taxes the server too much I can't see it being a practical solution.

What I do see as a practical step for now, is to set HSphere to make ODBC unavailable on all new 2K3 servers.

Personally, if I had the choice, I'd want all my stuff on a server which does away with ODBC once and for all.

[DVHost]

I always recommend to my clients that they use "Microsoft.Jet.OLEDB.4.0" as I believe it is more robust and the performance is much better connecting at the OLE layer not to mention processor bandwidth conservation.

After reading through this entire thread I decided to check my sites code to see what Dreamweaver MX was putting in there. In a nutshell here is what it codes:

---------------
Set rsget_MYDB = Server.CreateObject("ADODB.Recordset")
rsget_MYDB.ActiveConnection = "dsn=login-MYDSN;uid=1A2b345;pwd=1A2b345;"
rsget_MYDB.Source = "SELECT * FROM myTABLE"
rsget_MYDB.CursorType = 0
rsget_MYDB.CursorLocation = 2
rsget_MYDB.LockType = 1
rsget_MYDB.Open()
---------------

Notice that a provider or driver is NOT listed in code. In fact no driver or provider is listed in code anywhere in my site. So I gather something must be used as a default. When coding a connection manually (which I have not done in forever) I would typically include this code:

rsget_MYDB.Provider = "Microsoft.Jet.OLEDB.4.0"

Dreamweaver MX it seems, does not specify:

Driver={Microsoft Access Driver (*.mdb)} OR Provider=Microsoft.Jet.OLEDB.4.0

So, what is used as a default?

In addition, I thought I would mention that in the HSphere CP, when setting up an Access DB you must select from "Available ODBC Drivers". The one for Access is "Microsoft Access Driver (*.mdb) [add]".

Would this in effect mean that you are specifying the access driver instead of the jet oledb provider right from the CP, potentially nullifying any specification made in code? Could this also explain why I can connect to my access DBs without having specified a driver or provider in code or is a default being specified regardless and which one?

Would calling an Access DB using the method I have been using circumvent the problem with crashing?

So many questions...

[Brangwyn]

Your connection there is using a DSN which adds yet another layer of complexity to things, the DSN will be using the ODBC driver though seeing as thats the only option you can choose when creating the DSN.

DSN-Less connections are much faster generally speaking.

[antic]

Yes, by saying "dsn=..." in the connection string, you're specifying the name of the ODBC record that maps to your Access database.

The way I set up an Access database is simply to create it locally with MS Access, then upload it via FTP to my site, and then type in the correct connection string in my code (as newmem mentions above):

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/foldername/access.mdb")

There's no need to use HSphere at all to set up Access databases, and I'd recommend not to anyway as it seems to be ODBC-centric.