creating a linked server on a 64-bit SQL 2008 to the 32-MAS ODBC driver?

Unfortunately, if the driver is x86 only, you won't be able to create a linked server from an x64 version of SQL Server.

We have this same issue with one of our vendor databases - only an x86 driver is available, but we deploy x64 servers pretty much exclusively. This leaves you with a couple of options:

 - You can use the x86 version of SSIS to do scheduled data migration into a database on your x64 SQL instance. This isn't ideal, as it doesn't give you access to live data, and you can't write back, but if you just need to view data that's kind of current and you can set up a schedule that works, this might be the easiest way to go.

 - You can install an x86 SQL instance just for this dedicated purpose (what we did). You set up the linked server on that instance, since the driver is the correct build, and then you can either execute any necessary data access directly on that server, or you can set up a linked server to there from your production server, and then use OPENQUERY to execute 4-part queries against the x86 server (not pretty, but does the trick).

There's no way to directly access the data from your x64 sql instance using an x86 driver, unfortunately.

Ryan:

The first option you mentioned sounds attractive.  Would it be possible for you to walk me through this or give me some pointers on how to make this happen?  Is the 32-bit version of SSIS already installed with the 64-bit version (sort of like the 32-bit version of ODBC Admin)?  Or is this something that I would have to download and install separately?

I do not need live access to the data, nor do I need to update or write to the MAS90 database.  A daily snapshot will suffice.

Thanks for your help!!

Ryan:

I think I've figured it out....I'll reply in a few minutes with results.....

(not that you are anyone else is monitoring this thread for up-to-the-minute results :) )

Nope - did not work.  I was attempting to run the SQL Import Wizard and use the 32-bit System DSN that I had created.  It got to the point of listing all of the tables in my MAS90 DB, but when I tried to edit the field mappings I got an error message:

Index was outside the bounds of the array.

Not sure what, exactly, this means, but this was a dead-end.

That sounds more like an issue with the driver than with the Import Wizard - though do you really need to edit the field mappings, or were you just going to take a look at them and make sure they were correct?

That said, I believe the build of SSIS that is running depends on which version of SQL you installed first - if you initially deployed SSIS as part of an x86 SQL installation, you'll have x86 SSIS installed, but you'll have x64 if it was initially part of an x64 installation. Since you're working with an x86 driver, you'll need to make sure you're using an x86 version of SSIS. It doesn't need to be local, though - you can install an x86 version of SSIS on another server and run your package there (assuming your driver is installed as well).

I found and ran the Import/Export Wizard (32-bit) and still ran into the same "Index was outside the bounds of the Array" error.  I get this error when I try to edit mappings, and when I click "Next" after tagging which tables I want to import.

I can browse the data fine (it gives you this option when you are tagging tables to import) but it won't go any further into the import process.

I found a third-party application that solves this problem and will perform the copy for me on a scheduled basis.  I don't know the rules on the board about posting references to commercial products, but since I'm not a reseller or agent, I guess it's OK.

The product is WinSQL.  I tested it out and it works without any problems.

I suppose I could let the mods confirm, but I don't think there's a problem mentioning the tool you used to solve the issue (you could even provide a link, provided it's not an affiliate link).

I'm glad you found something that would help you resolve the issue!

This was the solution for me.