Automations

MSDAORA Linked Server Workaround

I ended up installing the 32bit Oracle Client to get the Provider: OraOLEDB.Oracle

my link between 64bit SQL Server to a 32bit Oracle server works great.

here’s my full link scripted out.

hope it’s useful

/****** Object: LinkedServer [OraLinkServer] Script Date: 12/13/2007 15:01:57 ******/
EXEC master.dbo.sp_addlinkedserver @server = N’OraLinkServer’, @srvproduct=N’Oracle’, @provider=N’OraOLEDB.Oracle’, @datasrc=N’MyServer.MyDomain.COM
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’OraLinkServer’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’MyUser’,@rmtpassword=’MyPassword’

GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’rpc’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’rpc out’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’OraLinkServer’, @optname=N’use remote collation’, @optvalue=N’true’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s