The ColdFusion MX ODBC Server service terminated unexpectedly.

[p2ataru]

Every one or two week running suddenly the service stop. Hope someone will help me to resolve this issue. Currently I'm using Coldfusion MX 6.1.
Thanks

[rwhaun]

I have the same issue where the CF7 ODBC Service stops on my Windows 2003 server and any CFM page that hits an Access database gives a timeout message.

Sometimes I can stop all CF7 Services, force kill the ODBC Server service, then restart all the services. Other times it won't restart the ODBC Server service until I reboot the machine.

We used to run CFMX 6.1 on a Windows 2000 box and had the same issue. I figured it was either a CFMX or Win2K issue that one go away once we upgraded. Now we have CF7 on Win2K3 and the problem still happens.

Another solution I tried was to upgrade all our old Access97 databases to Access2003. Didn't help.

If anyone has a solution please let me know!

[Guest]

I have the same problem. We have 4 coldfusion servers - all of them experience the ODBC Server service locking up.

Sometimes like rwhaun I can restart the service, sometime I have to reboot the entire pc. All boxes are windows server 2003.

Right now we have a scheduled job to check if odbc requests are responding, else generate a text message to the after hours pager, but it is getting very old being woken up at odd hours and the logs show nothing... !!!

[rwhaun]

I ended up using the following workaround suggested by Adobe (
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18800#error430http://www.adobe.com/cfusio...) and backed up by some support forums ( http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=1004844&enterthread=y).
Delete the *.trc event trace logs from the SequeLink server tracing directory, located at *cf7root*\db\slserver54\tracing\. Then restart the two ColdFusion ODBC Services.

I’ve done this once and it worked. However I'm not sure if this will prevent the timeout issue from ever happening again or if this is the solution to restarting the service after the timeout (normally Windows won’t let me restart the service and I have to restart the whole machine). Only time will tell. The good news is that I did this 10 days ago and haven't had a timeout since.

[ksmith]

I posted this a while back:
"Besides avoiding ODBC when possible you should really stay away from desktop databases like dbase and access. Microsoft themselves does not include msjet/odbc drivers for them in win2k3. They indicate they feel they are inappropriate for use on a hi-speed, multi-threaded appserver. I agree and the same logic applies to CFMX. Do not use desktop databases."

Microsoft is the vendor of win2k3 and Access. If they originally said do not use Access on their server, that should carry some weight. MDAC 2.8 and win2k3 did not contain the msjet or odbc drivers for access (dbase or foxpro, also.) I know lots of applications used to work in CF4 and CF5 with access. But your OS is 10 times faster now. Also your hardware is many times faster (1000's?). And of course, CFMX is thousands of times faster. Finally, your old MDB file is 'old' and probably many times larger then when you started.

The bottom line is only small single user applications are appropriate to run on Access and cfmx. If the ODBC client/server locks up on you regularly, it is time to step up to a real database.

[Flyguy1996]

I'm having similar issues with my shared web hosting service and CF ODBC DSN's just stopping working. It's all fine and dandy that no one recommends Access or file-based databases, but the REALITY is that most people use Access because it's free, it's easy, and it's available. Most people don't have MS SQL, Oracle, or MySQL nor the expertise to manage it, so they build their databases on Access. For a hosting company, you HAVE to offer Access support or you'll alienate 90 percent of your dynamic web site customer base.

So, please try to find a good way to resolve this issue. The stability of my servers (and my business) depend a lot on it.

[ksmith]

Hi Tony,
This is probably something Adobe cannot fix, unless we already have. If you are using CFMX 6.x you should update to the
3.5 JDBC drivers and to the latest version of Sequellink (5.4). CFMX7 is already at Sequellink 5.4.
http://www.adobe.com/go/42dcb10a
http://www.adobe.com/go/e917887

The next step in attempting to work with MS Access databases is to setup the datasource properly. All access datasources
should be configured with "maintain connections" set false and limit connections set true and limited to 1. Set these
properties in the advanced settings of the datasource.

The next issue, you imply in your own message, "nor the expertise to manage it". Access datasources are seldom
managed, also. The database file should be imported into a recent version of Access. You should not be using an old
Access 95 file that has just constantly been copied to new servers. You should also optimize access data files on a regular
basis. That is done in the windows odbc administrator. Do this offline and after making a backup.

Next make sure you users make reasonable attempts to limit unnecessary calls to the database. Access should NEVER
be used to store client variables. If access is your database, use cookies for client variables. Client vars in the
database cause a select at the start of every page and an insert at the end of each page. This is too much for access to
handle, right there.

Access users should use query caching where ever possible. I had an educational client who contacted me with ODBC
problems as described in this thread. It turned out they had client vars stored in Access. They also had a cookie trail
stored in the database that drove links at the top of all pages. Its purpose was to allow users to know where in the site they
were and to quickly navigate to previous sections. Again, this caused database access on every page. Yet this information
was very static - the site did not change very often. So caching this query eliminated much of their database traffic. Moving
client variables to cookies eliminated much more. Using these 2 changes they were able to get along with MS Access until
they could complete an upgrade to ms sql.

Another possible fix is to use the jadozoom JDBC driver instead of the ODBC client server. The jadozoom driver connects
to Access using a bridge from JDBC to Oledb. Some users have more success with it, then the odbc connection. But it is
not a golden bullet and will not work in all cases. See the techote, http://www.adobe.com/go/2d2a7a19.


Hopefully these suggestions will help. But that is it. Microsoft themselves does not recommend Access for production
application servers. There are other free and low cost options. That is the real direction you should go.

[Guest]

This is all fine and good for rwhaun - if he is using Access he should switch, but the databases that I use are DB2/iSeries (as400s) that I am accessing via ODBC.

I'm also using native connections to several Oracle databases - but these do not seem to be the cause of the lockups - as far as I can tell the lockups occur on/around the DB2 ODBC access.

What I have done as a temporary fix that I changed the datasource setting in the coldfusion admin to not maintain connections across requests, and set the timeout to 3 minutes. It has not locked up since

[tinu8805]

I strongly support standing up against the disparaging saying "ODBC is bad".

My native DB is Sybase Adaptive Server Anyhwere whose native driver is ODBC.

I currently cannot step up our productive box from Apache / CF MX 6.1 on Win2K to Apache / CF 7 on Win2K3 Standard R2. The Win2K3 box has all the latest patches applied which were distributed by Windows Update Service.

When I go to CF Admin -> Datasources, I currently cannot setup a new ODBC DSN. After choosing ODBC, the page tries to load the next dialogue but hangs. In Services, I cannot stop the CF ODBC Server. The only way to kill it is using SysInternals Top Tool Process Explorer. With it, one can kill the entire process tree which contains the ODBC server.

Now, Adobe should talk with MS and fix this annoying topic ...

Martin

[ksmith]

My point is ODBC connections are a bad choice for CFMX - a java program running on a J2EE server. And it is.

IBM makes several JDBC drivers for IBM DB2 and they have them for the iSeries. ODBC is not required and is a bad choice for connectivity from CFMX. Oracle has had JDBC thin and fat clients since Oracle 8i. Sybase has had JDBC drivers for ASA for over 6 years. There are also 3rd party solutions for JDBC drivers for these and most major DBMS's.

Years ago ODBC was a poor choice for database connectivity because most ODBC drivers did not allow you to exploit all the features of your DBMS. So everyone used 'native drivers' and avoided ODBC if possible. Now that is no longer the issue ODBC drivers generally have replaced native drivers.

The issue here is that connecting CFMX to a database thru ODBC means you make a JDBC connection to an ODBC client/server bridge that connects to windows ODBC subsystem that connects to the ODBC driver that may then go into the DBMS's database client software that finally goes over the network to your DBMS. JDBC allows you to connect directy from CFMX into the driver over the wire to your DBMS (type IV JDBC). JDBC works better with CFMX.

[tinu8805]

Ken,

I don't doubt that Java-based connectors are better for CFMX ... being itself a Java application. Connection speed and fully exploiting product-specific features is not always paramount. So, if the only way to connect is using ODBC the bridge must be there and working. And no evil words about that. That's what I meant.

Currently, Adobe does not prove mastership in this area on the Win2K3 platform. All those messages around the CF ODBC Server. And I am one of the victims.

I know the history about database interfaces ... I was using Gupta and Watcom in the early 90 when Microsoft launched ODBC. We all laughed and thought nobody would want to use an additional layer because everybody feared performance decrease. MS succeeded. Nowadays, it's going the reversed way.

Anyway, I hope that Adobe will manage soon that CF ODBC Server will run again flawlessly.

Well, so I am fored to leve ODBC which was always a good connector to ASA and did let me exploit almost all features of ASA. Trying to connect to ASA using jConnect has not yet worked as expected despite the Adobe KB. I admit, I am new to Java-based database connections, so I obviously have to face the learning curve. Again. I hope I will master the current problems.

Martin