Connect 32bit ODBC datasource to 64Bit MS SQL Server 2008

Posted on 2011-05-10
Last Modified: 2013-01-10
I would like to import data from a legacy database (Sybase Advantage 8.1) into Microsoft SQL Server 2008 on Server 2008 R2.  My preferred method for doing this would be to add the old database as a linked server in MSSQL and then write a few transformation and conversion scripts right there in SQL Server Management Studio.  The problem is that you are apparently not supposed to be able to access 32 bit ODBC drivers from 64 bit SQL server.  It seems to me like there should be some way to convert the calls and bridge that gap and that this would be a common problem, but I haven't found the solution.  I can make the 32 bit ODBC connection on a 64 bit system by running "c:\windows\sysWOW64\odbcad32.exe" manually, which works, for example, for accessing the data from Excel, but I can't figure out how to get to it from SQL Server.
Question by:garrun
    LVL 23

    Expert Comment

    - i've yet to experiene or experimenting such situation. however there's few people have done the 64bit access to 32bit by installing SQL server 2005 32bit side by side with 2008 64bit. import utility still would not work but you can use the construct query to import the data. read further here:
    LVL 16

    Expert Comment

    Well, Here's what i would suggest

    you will need to go to the Solution properties and in the Deployment utility Set the Property RUN64bitRunTime to FALSE... that will allow you to use ODBC connection set up using Wow64 ODBC driver

    Also, if you are running the Package as SQL Job , you will need to check the box to run the package in 32 bit Environment Mode

    LVL 21

    Accepted Solution

    Please use the import/export wizard of SQL Server

    Author Closing Comment

    This actually worked for me.  It wasn't really what I wanted to do, as setting up a linked server would allow for ongoing import, but using the import wizard solves my immediate problem.  There was a second problem confusing things having to do with the ODBC driver I was using, but once that was sorted, the import worked using the 32 bit driver, though linking still is unavailable.  I still don't know why this is, really - if the server can read the data for import, why can't it read it as linked?  Oh well...

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now