cancel
Showing results for 
Search instead for 
Did you mean: 

Please explain the ODBC Administrator situation in 64-bit Windows 7

Breck_Carter
Participant
20,748

I'm having zero luck getting the ODBC Administrator to work reliably with earlier versions of SQL Anywhere in Windows 7... one symptom is that Test Connect doesn't work, another is that the new DSN doesn't show up, another is the existing DSNs don't show up in the ODBC Administrator... soon, my head will be exploding.

The SQL Anywhere dbdsn utility seems to work just fine, and is a nice workaround for creating DSNs... and the old versions of SQL Anywhere are working fine (for my purposes), all the way back to 5.5.

Can someone please explain what the whole story is, with C:\\Windows\\system32\\odbc32adm.exe versus C:\\Windows\\SysWOW64\\odbcad32.exe versus whatever-other-versions-might-be-lurking?

Accepted Solutions (0)

Answers (4)

Answers (4)

johnsmirnios
Employee
Employee

I have a few things to add to what Justin posted but the size & formatting constraints on comments are annoying.

regsvr32 nowadays will automatically reinvoke the correct bitness of itself to match the DLL that you are registering.

It is very important always to use a fully qualified, absolute path to the DLL that you want to register or unregister.

There's some Microsoft Magic behind the scenes too. If both a 32-bit and a 64-bit version of an ODBC driver are registered with the same driver name (which is what SQLAnywhere does if you install both drivers), Microsoft treats them as equivalent except, of course, for bitness. That means, for example, you can create a 32-bit User DSN with the 32-bit odbcad32 then manage it with the 64-bit odbcad32. If only one bitness of driver is registered, you can only manage the DSN with the correct bitness of odbcad32. I recall that the installer team also noted that changing DSN information in the registry caused the OS to replicate the changes to other mystical areas in the registry and to reconcile the 32-bit & 64-bit versions of the registry. I remember watching it all happen in procmon and it was quite dizzying. It was problematic because the installer tool we used at the time monitored registry changes during a sample run then basically just replayed them for a real install. Of course, the install tool got very confused by the nonsense going on behind the scenes.

32-bit and 64-bit system DSNs seem to be kept separate: system DSNs created with the 32-bit odbcad32 never appear in the 64-bit odbcad32 and vice-versa.

To determine which version of odbcad32 you are using, look for odbcad32.exe in the Processes list of taskmgr and see if it has a " *32" after odbcad32.exe indicating that it is a 32-bit executable. Alternatively, just try adding a DSN: if you see the Excel, Foxpro, DBase, etc drivers, then it is 32-bit.

-john.

VolkerBarth
Contributor
0 Kudos

@John: "...if you see the Excel, Foxpro, DBase, etc drivers, then it is 32-bit." - Do you know if this is still true (w.r.t. Excel) for Office 2010 64-bit versions? (No, I don't own one...)

VolkerBarth
Contributor
0 Kudos

@John: Thanks for pointing out the User DSN "replication" - I wasn't aware of that (bad?) "magic" at all.

Breck_Carter
Participant

This is making me feel better, where "better" means "less alone", not smarter or anything like that 🙂

johnsmirnios
Employee
Employee

@Volker: I'm using Office 2007 so, yes, newer versions of Office may have 64-bit drivers. Thanks for pointing that out. Taskmgr is a reliable way of knowing the bitness of a process.

justin_willey
Participant

John Smirnios posted this a while ago on the general forum (relating to 2008 server I think - but it's the same in 7 I believe):

Actually, it depends which odbcad32.exe you run. If you are a 32-bit process, you will launch the 32-bit one. If you are 64-bit process, you will launch the 64-bit one. Remember that for a 32-bit process, c:\\windows\\system32 9where the 64-bit stuff is normally kept) actually redirects to c:\\windows\\syswow64 (which is the 32-bit stuff). From a 64-bit process, you can reference c:\\windows\\syswow64 to invoke 32-bit executables. So, from a 64-bit shell, you can run odbcad32.exe to get the 64-bit driver manager or run c:\\windows\\syswow64\\odbcad32 to get the 32-bit driver manager. From a 32-bit shell, you can only [easily] invoke the 32-bit driver manager because c:\\windows\\system32 is automatically redirected to c:\\windows\\syswow64.

Once you are sure you are launching the right odbcad32.exe and you still find that the ODBC drivers are not showing up for you, try reregistering them with "regsvr32 dbodbc9.dll". Remember to run the right (32-bit or 64-bit) regsvr32.exe just as you did for odbcad32.exe and point it at the right dbodbc9.dll.

-john.

John Smirnios Senior Software Developer iAnywhere Solutions Engineering

as I said at the time:

This does sound like another trimuph of the MS usability labs doesn't it - odbcad32.exe that'll be the 64 bit version then, err... unless it isn't.

VolkerBarth
Contributor

Just an addition - for further reference:

The newly-discovered SQL Anywhere Insider magazine has an in-depth article on this topic:

January 2011 Edition: Working with ODBC DataSources on Microsoft® Windows® (32-bit versus 64-bit)

Former Member

In SQL Anywhere 10, I found three reliable ways to avoid the problem.

One is to always use the shortcut to the ODBC Administrator from within the connection window in the Sybase tool (such as ISQL or SCJView). That seems to always pick the correct one. My guess is this works because these programs are 32-bit programs themselves and can't use the 64-bit tools, either.

Another is to go to the WINDOWS\\SysWOW64 folder and run odbcad32.exe. This is a pain because we occasionally forget and spend a silly amount of time trying to figure out why the ODBC tools are behaving in an inconsistent manner.

Finally, I see you've stumbled on same solution I finally found, which is to use the dbdsn command. This works flawlessly for us every time.

"C:\\Program Files\\SQL Anywhere 10\\win32\\dbdsn" -ws "<name>" -pe -c "dbn=<>;uid=<>;pwd=<>;dbf=C:\\Database\\<>.db;dbkey=<>;AutoStop=<>;START=<>;INT=NO;COMP=NO"

I'd love to be able to issue a personal thank you to whoever put dbdsn in the toolkit in the first place. It's really useful.