Amy (amyboyd) wrote in sqlserver,
Amy
amyboyd
sqlserver

How to make an Oracle Linked server on SQL 2000

Posting for the benefit of all after personally pulling my hair out on this one...

1) Installed the Oracle Instant Client following these wonderful directions:
http://www.dbatoolz.com/t/installing-oracle-instantclient-basic-and-instantclient-sqlplus-on-win32.html

2) Restarted my SQL server to re-load the ODBC drivers

3) Created linked server

USE master
go
EXEC sp_addlinkedserver @server=N'ORCL_SRVR', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'ORCL_SRVR'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='collation compatible', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='data access', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc out', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='use remote collation', @optvalue='true'
go
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ORCL_SRVR', @useself='FALSE', @rmtuser=N'system', @rmtpassword=N'my password'
go
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname=N'ORCL_SRVR')
PRINT N'<<< CREATED LINKED SERVER ORCL_SRVR >>>'
ELSE
PRINT N'<<< FAILED CREATING LINKED SERVER ORCL_SRVR >>>'
go
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 2 comments