March 10, 2014 at 7:44 pm
Hello,
After a lot of research on the web, books online, and SQL Server Central's forums, I haven't seen a clear answer yet to this challenge -- how to get SQL Server 2012 (or any of the earlier versions) to leverage the Windows 7 subsystem for 32 bit based ODBC drivers?
From Excel, for example, reaching into SQL Server, employing the subsystem is clear enough. As for the other direction, not so much.
Has anyone seen an article yet that provides a step by step guide to bridge the gap? I must be missing something -- i.e., my assumption is Microsoft would provide some method of allowing legacy ODBC drivers which do not yet have a 64 bit version to continue working in the 64 bit world -- especially in light of its push away from OLE DB to ODBC.
Thank you.
March 11, 2014 at 12:54 am
You can still use them pretty easily within SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2014 at 4:00 am
"You can still use them pretty easily within SSIS."
Thank you, Koen. A colleague mentioned that.
However, that still leaves us without a fundamental SQL Server feature -- database link connections to legacy sources dependent on 32-bit ODBC drivers. It means rebuilding operations based on these legacy sources. The prospect of that effort is significant.
For now, it looks like the only cost effective option is to find a way to dedicate a machine to operate in 32 bit mode and link it to a 64 bit based farm.
March 11, 2014 at 10:39 am
You should be able to setup 32bit DSNs by using the ODBCAD32.exe in the C:\Windows\SysWOW64 folder.
March 11, 2014 at 10:57 am
Ken McKelvey (3/11/2014)
You should be able to setup 32bit DSNs by using the ODBCAD32.exe in the C:\Windows\SysWOW64 folder.
Thank you, Ken. I should, I can, and I did. 🙂 Unfortunately, those DSNs are, in effect, refused by SQL Server.
I experimented with creating the DSNs in the user tab as well as the system tab (where I normally place them). I followed Microsoft's recommendation to append _32 or _64 to facilitate distinguishing one version from another.
The wall I hit is marked by the "Microsoft OLE DB Provider for ODBC Drivers" when it attempts to call DSNs. So far, it look like that part of setting up a database link will accept only 64 bit drivers. 32 bit drivers, for now, are not invited to the party.
March 12, 2014 at 8:08 am
Dennis Baldwin (3/11/2014)The wall I hit is marked by the "Microsoft OLE DB Provider for ODBC Drivers" when it attempts to call DSNs. So far, it look like that part of setting up a database link will accept only 64 bit drivers. 32 bit drivers, for now, are not invited to the party.
Sorry, I misunderstood what you were attempting to do.
I doubt if Microsoft will ever allow the use of 32 bit ODBC drivers from within the 64bit engine as it sounds very inefficient.
Personally, I would look at SSIS but the 32 bit engine approach should work as well.
March 12, 2014 at 8:29 am
Ken McKelvey (3/12/2014)
Sorry, I misunderstood what you were attempting to do.
I doubt if Microsoft will ever allow the use of 32 bit ODBC drivers from within the 64bit engine as it sounds very inefficient.
Personally, I would look at SSIS but the 32 bit engine approach should work as well.
No problem, Ken. Thank you for the follow-up.
Unfortunately, the SSIS route would mean, in effect, rebuilding legacy operations. That is not a cost effective option for my shop right now.
For our transition into 64 bit operating systems, it continues to look like our most practical option for now is to set up a "bridge" -- that is, a Windows 7 machine configured to work in 32 bit mode only to manage legacy operations until we can justify the cost of rebuilding them with newer tools or, where possible, retire them outright.
Thanks again for your time and help!
March 13, 2014 at 7:55 am
I recently encountered the exact same problem. I needed to connect to a remote 32-bit Oracle database from a new install of 64-bit MSSQL. I didn't have the 64-bit ODBC drivers available, so tried with the 32-bit drivers; the DSN works fine, but 64-bit MSSQL refuses to work; error was "The specified DSN contains an architecture mismatch between the Driver and Application".
In the end, I managed to find a 64-bit ODBC Oracle driver, which solved the problem. I think you are correct, if 64-bit ODBC drivers don't exist, you're done for.
I also had some fun, trying to install 64-bit ACE OLEDB (Access database engine) drivers on top of the 32-bit Office - which is not allowed 🙁
I'm not a hardware expert, but I guess there is some fundamental architecture issue which prevents 64-bit MSSQL from using 32-bit DSN/ODBC. Other areas don't seem to be a problem - for example, my 64-bit MSSQL is quite happy to be configured by my 32-bit SSMS!
March 13, 2014 at 7:58 am
Andy sql (3/13/2014)
I also had some fun, trying to install 64-bit ACE OLEDB (Access database engine) drivers on top of the 32-bit Office - which is not allowed 🙁
It is possible though: Force installation of 64-bit ACE OLE DB provider[/url].
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2014 at 7:02 am
Yes, the /passive command-line-install workaround did solve the problem. That's what I referred to as "fun".
April 15, 2014 at 6:36 am
I'm happy, though puzzled, to report a solution to my own question:
1. Use the 32-bit ODBC subsystem to configure a 32-bit ODBC driver and add it to the System DSN tab.
2. Reference the 32-bit DSN name in SQL Server (any version 2005 and up) set up screen.
That's it. It should have been that simple from the beginning.
That said, something changed on my test machine. Previously when I did this, the DSN would appear in the system DSN tab of both the 32-bit ODBC manager and the 64-bit ODBC manager. Now, the 32-bit ODBC DSN appears only in the 32-bit ODBC manager.
In any event, this is a significant breakthrough for our shop, simplifying and accelerating our next steps.
Thanks for your help and attention!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply