January 25, 2008 at 10:58 am
We are going from 32-bit to 64-bit servers and I am having a very difficult time with linked servers. Linking to other SQL Servers is fine and dandy but I cannot link to an AS/400 or Oracle database. On the 32-bit box the best provider for both was the Microsoft OLE DB Provider for ODBC drivers, but it doesn't exist in the 64-bit version. From the research I've been doing many other people are experiencing the same problem. I found an article that says this particular provider will be available in Longhorn Server Beta 3, but that doesn't help me any. I've also noticed that it is recommended to use OLE DB instead of ODBC but the OLE DB drivers that I installed aren't seen by SQL 2005. The iSeries and Oracle 9i drivers are supposed to be for both 32- and 6-bit environments.
Has anyone found any good workarounds?
March 4, 2008 at 7:08 am
I had similar problem. Here is the solution that worked for me
Note1:we have SQL2005 St Ed - 64bit version with SP2 installed, Windows 2003
Note2: we installed iSeries V5R3 with one error message #1633, but you can ignore this message
Go To MS SQL Server Management Studio
1. Add linked Server using IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
2. Product Name: I entered AS400
3. Data source: IP of your AS400 box
4. Catalog: Enter your AS400 database...it looks like S1052...
Go To Security Tab and select the last option 'Be made using this security context'
Remote login: (Must be user id that has permission to access AS400 files and is in the AS400 ODBC group)
Click OK to save changes.
Expand Providers folder (listed under Linked Server) and right click on IBMDA400. Select Properties, and set all options to be enabled. You should see your linked server on the list at the bottom.
To test this new linked server, open new query window and run this:
SELECT * From OpenQuery(AS400,'Select * FROM YourAS400Lib.YourAS400File')
If you get results, you are all set.
I hope this helps. Good Luck!
March 4, 2008 at 7:45 am
Thanks for the info. I'll give this a try. I haven't had much time to experiment because the servers had to go into production quickly. It looks like it will work, though. Thanks again.
March 5, 2008 at 1:08 am
As for Oracle you'll need the 64-bit versions of the Oracle drivers installed to use linked servers - depending on your needs you may well need to install both the 32 and 64 bit drivers and update tnsnames, etc. in both directories... basically if the caller is 64 bit it will use the 64-bit driver (e.g. SQL Server 2005 core database engine, linked servers), if the caller is 32-bit (e.g. SSIS) it will use the 32-bit driver.
MS is no longer in the business of writing Oracle drivers and all drivers for Oracle should be obtained/downloaded from Oracle, not MS. MSDORA was written for Oracle 8 - and won't work with versions of Oracle past 10 (Oracle uses -2 versions as their supported version, e.g. drivers that work with 8 will continue to work through version 10, after which all bets are off) and does not come in a 64-bit version.
Joe
March 5, 2008 at 5:27 am
Thank you. I had tried the Oracle 9i driver which is supposed to be for 32- & 64-bit servers, but it didn't work. I've heard that others have had success with version 10 & 11 but I haven't tried them yet. It's kind of difficult to test when I don't currently have a 64-bit server 🙂 Thanks.
July 8, 2008 at 5:23 am
We also can't create a link server to Oracle 10.2.0.1 64 bit.
We don't see any oracle provider in the list.
We have installed only the Oracle 64 bit client because all our Oracle servers are 64 bit.
How can we make sql server 2005 see the installed oracle provider?
We use windows 2003 64 bit. and Sql server 2005 sp2 64 bit.
July 8, 2008 at 5:32 am
I was able to install the Oracle 64-bit drivers and successfully created a linked server using Oracle Provider for OLE DB. Make sure you uninstall any older Oracle products because that seems to cause problems. Also, I had to use the fully qualified server name in the linked server properties. Check your tnsnames as well. It should work.
July 9, 2008 at 7:52 am
I was also able to create a linked server to the AS400 with the iSeries 64-bit driver. It appears that the solution to linked servers on x64 servers is to install x64 drivers 🙂
July 9, 2008 at 8:02 am
What are exactly the text values that you are filling in the link server creation screen with oracle provider for oledb?
July 9, 2008 at 12:54 pm
Linked server name: Whatever
Proider: oracle provider for OLE DB
Product name: MSDAORA
Data source: Servername.xxx.xxx.net
The connection failed with just the server name but when I changed it to the fully-qualified server name it was fine.
July 10, 2008 at 5:14 am
what about provider string field?
July 10, 2008 at 4:34 pm
Hi,
I had problems with building a linked server using the installed drivers of Client Access for iseries on a x64 machine. I could solve the problem using the OLEDB provider for ODBC and doing the connecting over this. Though the connection is "bridged" the performance is very good.
Maybe this is a workaround for your driver issues on x64. It is a free download at MS:
This is a good site for the theme database drivers on 64bit.
Especially the 64-bit Story provides a good backround why so many problems occur with those drivers.
SK
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply