How do I connect to my 64-bit SQL Server with ODBC?

Question

I recently installed SQL Server 2008 Express on my Windows 7 Ultimate x64 home machine. I also have IIS 7.5 with PHP 5.3, and I was trying to connect to SQL via ADODB, but kept getting this error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application

After doing a small amount of digging on the internet, I think this is because the SQL Server ODBC driver is meant for 32-bit operating systems, and mine's 64. First of all, am I correct? Is this the reason I'm running into trouble? Secondly, if so, how do I fix this? Are there any updated ODBC drivers that work with 64-bit operating systems? I looked but was unable to find any...

Answer 1

You're right in that it has to do with the bits.

Hope this helps

--From MSDN --

To manage a data source that connects to a 32-bit driver under 64-bit platform, use c:\windows\sysWOW64\odbcad32.exe. To manage a data source that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe.

If you use the 64-bit odbcad32.exe to configure or remove a DSN that connects to a 32-bit driver you will receive this message.

Answer 2

I'm betting you are working with User DSNs.

Depending on your perspective, there's a "feature" or "bug" in the 64-bit Windows environment --

1. 32-bit User DSNs show up in the 64-bit Administrator and when 64-bit client applications ask for all available DSNs -- even though the 32-bit DSNs cannot be used by the 64-bit client app and Adminstrator.

2. 64-bit User DSNs show up in the 32-bit Administrator and when 32-bit client applications ask for all available DSNs -- even though the 64-bit DSNs cannot be used by the 32-bit client app and Adminstrator.

The error message you describe comes up anytime there's such a bitness mismatch between the DSN and the client trying to work with it.

Microsoft's recommendation is to name your User DSNs with _32 or _64, depending on the bitness of the driver on which they're based ... or stick with System DSNs.

There are 32-bit and 64-bit solutions for the connection you want. The bitness of your client application(s) -- IIS & PHP, in this case -- dictate the bitness of the solution you require.

Answer 3

MkUltra: - u are a rock star. I had the same problem and ran ODBCAD32 from sysWOW64 folder and it now works!! Thanks a million.