I recently had the need to access data in Oracle from SQL Server 2005. I was surprised to find out that there weren’t that many places in the web that would explain how this is done. I had to go through post after post gathering crumbs of information until I finally got it to work.
The concept is rather simple:
- You link the Oracle server
- You then query it like you would any other linked server
- select * from [ServerName].[TableName]
The problem, however, is the gotchas along the way.
There are two stored procedures that help in the first step of linking the servers: sp_addlinkedserver and sp_addlinkedsrvlogin. They do what their name implies, one creates the server record in the system table and the other one creates a log in with which you will be accessing the linked server’s data.
The stored procedure:
Create PROCEDURE [dbo].[Create_ServerLink]
ServerName: the name of the linked server. It is the name to be used in the statements to call data from oracle.
Schema: the name of the schema
Database: the name of the database
declare @ServerExits bit
set @ServerExits = (select count(*) from sys.sysservers where srvname = @ServerName)
if @ServerExits = 0
EXEC sp_addlinkedserver @ServerName , @Database, ‘OraOLEDB.Oracle’, @Database
EXEC sp_addlinkedsrvlogin @ServerName, ‘FALSE’,NULL, @UserName, @Password
Once linked you would access the server in the following manner:
select * from LinkedServerName..SchemaName.TableName
So if your linked server is called OracleData, the Schema name is Orion and the table name is Products then you would have the following:
select * from OracleData..Orion.Products
- You can link a server by going to the Server Objects\Linked Servers in SQL Server Management Studio, but I think this way is much better because you can add this stored procedure to any other stored procedure that uses oracle data. This is specially useful in an evironment where schemas are added constantly (like where I work).
- At first I used the MSDAORA provider which ended up being dirt slow. After doing some research I found that the OraOLEDB.Oracle driver is much faster.
Remember to let me know if this helped you in any way!