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
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
