December 4, 2021 at 5:55 am
Hi Experts,
I am getting the following error when i am trying to test on Linked server created on Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Standard Edition (64-bit) to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
The test connection to the linked server failed.
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
The OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXX" reported an error. The provider reported an unexpected catastrophic failure.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXX". (.Net SqlClient Data Provider)
Following the Script for Linked Server creation
EXEC master.dbo.sp_addlinkedserver @server = N'XXXXXX',
@srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'
xxx.xx.xx.xx:1521/xxxxxx'
/* For security reasons the linked server remote logins password is
changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'XXXXXX',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxxxx',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'rpc',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'rpc out',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'use remote
collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'XXXXXX', @optname=N'remote proc
transaction promotion', @optvalue=N'true'
GO
December 5, 2021 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply