Friday, September 11, 2009

How to: adding linked server for Oracle in MS SQL 2008

Step 1: enable "AllowInProcess" for provider "OraOLEDB.Oracle"


USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO


Step 2: add one linked server


Linked server: Test (here is name of your linked server)
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data source: Test (here is the tns name for Oracle database)
Provider string: (empty)

Security tab:
Remote login: system
With password: (system password for oracle)

Step 3: Verify

Perform selection from remote Oracle databse:


select * from [TEST]..[SYSTEM].[T]
go

update [TEST]..[SYSTEM].[T]
set username='TEST' where sid=143;
go

select * into test1
from [TEST]..[SYSTEM].[T]
go

select * from test1
go

delete from [TEST]..[SYSTEM].[T]
go

insert into [TEST]..[SYSTEM].[T]
select * from test1
go

select * from [TEST]..[SYSTEM].[T]
go


Caution: If you hit following error message, please go to step 1


The OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST". (Microsoft SQL Server, Error: 7399)