Thursday, January 12, 2006

SQLAllocHandle on SQL_HANDLE_ENV failed

Ah, the ever wonderful:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

The error happens on a brand new Oracle 9i installation on a Win2K IIS server. The specific context is a web-based database query to Oracle via the Microsoft ODBC driver. There are a few steps that have solved the problem for me:
1) reinstall the Oracle client. Specifically:
- Remove all the Oracle components
- Delete the Oracle9i directory
- If you've made a request to the IIS server that includes a call to Oracle, IIS will be hanging on to oci.dll, the Oracle Call Interface driver, so you'll have to reboot in order to delete the locked file. Reboot!
- After rebooting and removing the Oracle9i directory, reinstall Oracle.

2) Make sure to assign IUSR_servername R&X permissions on the Oracle9i directory

3) Edit the following key: HKEY_LOCAL_MACHINE\ROOT\SOFTWARE\Microsoft\MSDTC\MTxOCI
Change the values of the three keys:
a. OracleOciLib - change from ociw32.dll to oci.dll
b. OracleSqlLib - change from SQLLib18.dll to orasql9.dll
c. OracleXaLib - change from xa73.dll to oraclient9.dll

4) Reboot again!
5) Test to make sure you don't get the evil error.

GOOD LUCK!

No comments:

Feel free to drop me a line or ask me a question.