Tuesday, May 05, 2015

ERROR [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

We had migrated an ASP.Net web application hosted in a Windows 2003 server to a new Windows 2008 R2 server.  Everything went smoothly until someone test a data upload function.  The data upload function takes a CSV data file and upload the data in it to a SQL Server.  When trying to execute the upload function, the following error pop-up.

Error

After tracing through the source code, I found out that it requires the “Microsoft Text Driver (*.txt; *.csv)” ODBC driver to work.  This is available in the old Windows 2003 server (32 bit) and also available in the 32 bit ODBC version of Windows 2008 R2 server (accessible through c:\Windows\SysWOW64\odbcad32.exe).

32bitODBC

However, this is not available in the 64 bit ODBC version of Windows 2008 R2 server.

64bitODBC

There are two methods to resolve this issue.

Method 1

Go to the “Advanced Settings” of the IIS Application Pool that the web application belongs to.

Set the “Enable 32-Bit Applications” to “True”.  This setting will cause the web application to use the 32 bit ODBC version in the Windows 2008 R2 server.

Enable 32 Bit Application

Method 2

Download and install “Microsoft Access Database Engine 2010 Redistributable” (AccessDatabaseEngine_x64.exe).

Installing “Microsoft Access Database Engine 2010 Redistributable” will add four Microsoft Office 64 bit ODBC driver.  The one that we are interested in is the “Microsoft Access Text Driver (*.txt, *.csv)” driver.

64bitODBCwithTextDriver

One important thing to take note is the name of the driver is not exactly the same as its 32 bit counterpart.  The 32 bit version is “Microsoft Text Driver (*.txt; *.csv)”.

This will require updating the code to use the new driver’s name “Microsoft Access Text Driver (*.txt, *.csv)”.

If there is no way to change the code, the other option is to rename the driver from “Microsoft Access Text Driver (*.txt, *.csv)” to “Microsoft Text Driver (*.txt; *.csv)”.  The name can be renamed through registry.

Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI

Rename the key “Microsoft Access Text Driver (*.txt, *.csv)” to “Microsoft Text Driver (*.txt; *.csv)”.

Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers

Rename the name of the string value “Microsoft Access Text Driver (*.txt, *.csv)” to “Microsoft Text Driver (*.txt; *.csv)”

After the registry change, you will see the name has changed in the “ODBC Data Source Administrator”.

64bitODBCwithTextDriverNewName

No comments: