asked on

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

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.

OP_Zaharin

- 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:

http://www.sqlservercentral.com/Forums/Topic590110-149-1.aspx

vdr1620

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

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garrun

ASKER

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...