message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". [SQLSTATE 01000] (Error 7412). The step failed.

Message

Executed as user:. Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG". [SQLSTATE 42000] (Error 7303)  OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". [SQLSTATE 01000] (Error 7412).  The step failed.

SQL jobs failing with above error message. This is Oracle linked server issue.

To double check that go to Server Objects-> Linked Servers and right click on Oracle linked server used by the SQL job and click on test connection.

You will see below error message.

TITLE: Microsoft SQL Server Management Studio

------------------------------

The test connection to the linked server failed.

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG".

OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4232&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------

BUTTONS:

OK

Contact Oracle team to fix the Oracle instance issue.