4

I have a Windows 7 64bit system that must run a 32bit application that accesses a MSSQL Server 2005 express via an ODBC connection. After installation everything was working fine. Now a few weeks after installation I get a report that the application is no longer connecting to the database. I checked the C:\Windows\SysWow64\odbcad32.exe to see if there is a problem with the ODBC settings, and the ODBC connection was no longer visible. So I checked the registry to see if it was completely removed. The registry key is HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC When I opened that up the values were there, they just weren't accessible to the odbcad32 or my own application.

I was attempting to run this as a computer administrator so permissions shouldn't be an issue, and besides I checked to make sure that they weren't changed, but I still can't get it to work. Does anyone have any ideas on how to get the values back into the odbcad32 applet? That will most likely fix the software problem as well.

4
  • Have you tried creating a new connection, or perhaps deleting the old one from the registry and recreating it via the applet?
    – Karan
    Oct 26 '12 at 16:41
  • @Karan it seems it was a two fold problem. First the database file itself was corrupted which required a complete database rebuild. Then I did what you suggested, by removing the old DSN entry and re-created it and now it is working fine. What I still would like to know is "why" did this happen?
    – JRSofty
    Nov 7 '12 at 7:30
  • 2
    Have you tried running 32bit odbcad32 from c:\windows\SysWOW64\ instead of just running from default location c:\windows\system32\ .
    – week
    Nov 12 '12 at 18:26
  • 1
    Yes, I should have mentioned it. For this software I always run it from c:\windows\SysWOW64
    – JRSofty
    Nov 12 '12 at 18:26
7

I think this article on my employer's site, about using 32-bit ODBC on 64-bit Windows, may help you out...

Nothing you've said, in your comment below nor in your original problem statement, clearly indicates that you're using the 32-bit ODBC Administrator. Note that both the 32-bit and 64-bit program are named the same, odbcad32.exe. The difference is in their counterintuitive directory location -- System32 (for 64-bit components) and SysWow64 (for 32-bit components).

You may be hitting an old but recurring issue with minor corruption in the Windows Registry. The corruption takes the form of entries containing this 4-character string --

@=""  

These entries aren't visible anywhere except Registry export files -- but they can lead to a number of undesired behaviors.

NOTE: On your 64-bit Windows machine, there are naturally some complications tied to the 32-bit Registry. This Microsoft KB article may be sufficient to get you through these.

I suggest that you use the 64-bit Registry Editor to export the following branches (where these entries tend to be found) --

HKEY_LOCAL_MACHINE\Software\ODBC
HKEY_LOCAL_MACHINE\Software\WOW6432Node\ODBC
HKEY_CURRENT_USER\Software\ODBC
HKEY_CURRENT_USER\Software\WOW6432Node\ODBC

Edit these files in any text editor (Notepad.exe or Wordpad.exe are generally fine), and delete all lines which consist of that 4-character string, above. Then, delete the Registry tree segment(s) you exported, and import from the edited files -- thereby restoring the tree segment(s), minus the corruption.

4
  • While the information you give is correct that is not my problem. The application's installer does create a 32bit ODBC system DSN, and the application is installed in the `C:\Program Files (x86)` folder. The problem is that it wasn't shown in the 32bit ODBC administrator applet even though it was in the registry under the correct location.
    – JRSofty
    Nov 7 '12 at 7:32
  • I've edited my answer. You may find what I was saying about the 32-bit vs 64-bit administrator clearer now ... and I've added some more information about another possibility.
    – TallTed
    Nov 12 '12 at 18:09
  • Thanks, you are absolutely right, I should have mentioned that I was using the odbcad32.exe from the SysWow64 folder. I'm not sure what you mean by the 64bit Registry Editor. Normally I just enter regedit in the Start menu's search bar and open that. That may or may not be the 64 bit version. In any case I never saw a @="" entry there. Must I export the branches to see that entry?
    – JRSofty
    Nov 12 '12 at 18:21
  • Yes, you have to export the branches to text files, to see the @="" entries. They are not visible in the RegEdit GUI. And, as the MS KB article I linked to says, Start -> regedit will get you the 64-bit Editor on 64-bit Windows. You have to specify the SysWow64 path to get the 32-bit Editor.
    – TallTed
    Nov 12 '12 at 18:36
2

I had this same issue and here's how I fixed it for my System DSN.

For reference, here are the two paths in the registry for whether the ODBC is 64-bit or 32-bit on a Windows OS.

64-bit:

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

32-bit:

\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI

In the ODBC.INI folder there was a sub-folder with the name of the DSN with all its related keys, and another sub-folder called ODBC Data Sources that only had the (Default): (value not set) key.

In the ODBC Data Sources folder there should also be a key with the Name of the DSN entry, Type of REG_SZ and a Data value of the ODBC Provider/Driver name (in my case it was iSeries Access ODBC Driver). I set up another test to an MS SQL server, and the Data value was SQL Server.

How I discovered this was I completely deleted all DSN folders and the ODBC Data Sources folder and recreated my DSN from scratch. It created both folders and related keys, and now my DSNs are showing up and are usable.

Without the key with the provider name in the ODBC Data Sources, the DSN would not show up in odbcad32.exe. I could also recreate the issue by deleting the ODBC Data Sources entry.

Hopefully this helps someone if the other solution doesn't fix them. I tried exporting my registry keys and did not see the @="" as described in the other solution. Please let me know if these instructions aren't clear enough and I'll try to post screenshots. Thanks!

1
  • The ODBC Data Sources key was definitely my problem.
    – JamesCW
    Apr 9 '15 at 12:32
0

Just as like @TallTed said, the issue is related to the double-version of the ODBC Administrator released in x64/IA64 Windows 7 Windows XP (and later).

I personally found this great official KB article that really helped me to overcome the issue; your specific issue is also addressed in another KB post here. I also tried to summarize this specific scenario here.

BTW, the problem has been (quite) fixed in Windows 8 and later, since the Control Panel's Admin Tools folder now holds two different ODBC Administrator icons: one for 32-bit, one for 64-bit. Not a solution, but at least an average user will be able to sort things out.

I don't know why the same workaround hasn't been applied to Win7 as well. (MS said something related to a retro-compatibility issues near the end of the second KB entry I mentioned, but I just cannot understand how adding an icon would break anything.)

-1

Eventually found resolution to apparent failure to install an 32 bit ODBC driver some 4-5 years ago. Answer 7 above points to the problem "The difference is in their counterintuitive directory location -- System32 (for 64-bit components) and SysWow64 (for 32-bit components)." On running the odbcad32.exe to display the ODBC Data Sources window, it became apparent the Control Panel command runs the 64 bit version in System32\, despite the label "ODBC Data Sources (32 bit)". I also checked Registry to find relevant key data properly installed at HKLM_Software_Wow6432Node.

My solution is to create shortcut to C:\Windows\SysWOW64\Odbcad32.exe on my Desktop. This then displays the ODBC Data Sources window for the 32 bit drivers. On my Win7 Professional OS installation the Drivers tab lists 25+ 32 bit drivers.

Conclusion - Microsoft gave us a command which points to the wrong folder, presumably confused by their counter intuitive folder naming.

2
  • 3
    This should have been submitted as a comment to "answer 7". Since you do not have enough reputation to submit a comment, it means, you simply should not submit comments, until you have the appropriate privilege I have no idea which answer is "answer 7", we also have no idea what command you used, which is the reason I am treating this as a comment submitted as an answer
    – Ramhound
    Apr 16 '18 at 18:54
  • I think he was referring to the one with 7 votes above. However you are correct this should be a comment.
    – JRSofty
    Apr 17 '18 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.