When I try to add a linked server in SQL Server Management Studio (SSMS), I can't see the Oracle provider in the drop down. However, it's there in Business Intelligence Development Studio. I can connect to my Data Source just fine and report of our database.
I'm running SQL Server 2008 R2 64 bit on Windows Server 2008 R2 Enterprise 64 bit.
However, SSMS is a 32 bit application so don't know if this has anything to do with it.
I installed a 32 bit client but this still wasn't available in the provider drop down in SSMS. I downloaded ODTwithODAC112021 from this page http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
When I did download it I tried to create a linked server using a script but it didnt recognise oledb.oracle.
So you currently have both 32 bit and 64 bit installed? Even though SSMS is 32 bit, when you're trying to create a linked table for example, you're creating a linked table for the 64 bit SS instance, so you need to have 64 bit client for that part. You're not actually working with oledb in the 32 bit app in that case, you're just using it to connect to the 64 bit SS.
Thanks for your reply. Sorry for the delay in getting back to you. I'm at work now so will try and tackle this issue today.
Greg, so what is the best way to create a linked server in this instance? I'm really struggling here. I am configuring SSRS for my company but have little experience in doing this so relying on Google.
The problem I have is when I navigate to my report server and try to run a report, it's giving me an error saying oledb.oracle.1 is not registered on the local machine. Im thinking that if I can get Oracle into SSMS then this will solve the SSRS error.
I can't offer much advice with respect to your requirements, as I know nothing about SSRS.
With respect to "oledb.oracle.1 is not registered on the local machine", it's almost always a matter of having the wrong bitness of Oracle client software installed. Once in a while it ends up being an install issue where the right bitness is indeed installed, but either oraoledb is not installed, or was installed but did not get correctly registered during install (which should happen automatically).
95% of the time though, it's due to having the wrong bitness of client installed. The very first thing to do is determine if the process trying to load oraoledb is 32 bit or 64 bit, and that may be different than the tool you're using to interact with that process.
Er, well, there is one more possibility. There's a known install permissions issue in 10201 that prevented non-administrator users from using the Oracle client, and results in the same error message. What version of Oracle client are you using? If 10201, that may be the cause, and is resolved by patching up to 10205.