Tired of the Crappy MS Oracle OLDEB Provider?

February 27, 2008 on 11:43 am | In .NET Coding |

If you use Linked servers in SQL Server 2005 to connect to oracle servers, and have been frustrated by the slowness of the Microsoft OLEDB provider for Oracle, then we have something in common.

Lucky for you and me, there is a fix.

Step 1: Install the Oracle Client freely downloadable from their website on your SQL server host. (Requires a little spam info.)

Step 2: Configure your client by setting up your TNSNames.ora file as per usual.

Step 3: in Management studio, login as a server admin (sa, or someone with the server admin role)

Step 5: Issue the following commands, (it’s in secret code, so be careful!)

That’s it kids! Just say no to the wizard.

You can then create a view in one of your existing databases that selects from this Link, and almost as magically every tool that hits SQL server can now be using SQL Server as a conduit for Oracle. (It’s better to do this if you’re combining data or something, not just as a very expensive DB provider of course, but it still works however you like it, and the chances are you’ve got another SQL host somewhere that wouldn’t mind pulling some data for your developers convenience.) My FAVORITE feature about doing this is that I can now do LINQ to Oracle. So there! Go Forth, Be Multiply, and Fruitful.