0

Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle server I have tried has the ORA-01804 issue. The actual message is "Error while trying to retrieve text for error ORA-01804" but I figure the ORA-01804 is the issue.

TNSPING to the name in @datasrc uses LDAP to resolve and resolves fine. (note: there is no tnsnames.ora file, ldap.ora and sqlnet.ora files were copied from the source server where everything is working.)

TOAD for Oracle has no trouble connecting to the database using that name as well.

SQL Server (source server is 2016, target is 2019, dev machine but same result on actual server):

SQL Server: 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 18363: ) (Hypervisor)

Oracle:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0

I had scripted the linked server connection from the server being migrated so everything should be equal (names changed to protect the innocent):

EXEC master.dbo.sp_addlinkedserver 
    @server = N'ORACLE_SERVER', 
    @srvproduct=N'Oracle', 
    @provider=N'OraOLEDB.Oracle', 
    @datasrc=N'orasrv'

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'ORACLE_SERVER',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'orasrv',
    @rmtpassword='redacted'

I'll skip the options, but they are there as well.

Improve this question
1
  • "Error while trying to retrieve text for error " is almost always a result of ORACLE_HOME not being set correctly, thus not being able to locate the message file to report the real error. However, it is quite likely that the real error also has a root cause in ORACLE_HOME not being set correctly - in the session of the process that has the error. Incorrect ORACLE_HOMED causing your 1804, then when oracle tries to report it, the incorrect ORACLE_HOME also causes the second error. Try googling ORA-1804. I did, and seems to point back to other oracle env vars not being correct.
    – EdStevens
    Mar 4, 2021 at 17:32

1 Answer 1

Reset to default
1

To test Linked Server connectivity, first reboot, and then test OleDb connectivity with 64-bit PowerShell. EG

PS C:\> $con = new-object system.data.oledb.oledbconnection("provider=OraOledb.Oracle;data source=N'ORACLE_SERVER;user id=xxxxxxx;password=xxxxxxx")
PS C:\> $con.Open()
Improve this answer
1
  • I can make the connection in PowerShell but still cannot add the linked server.
    – Metaphor
    Mar 4, 2021 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.