We recently migrated from one sql server to another. So all the workload, Tables, Jobs, Linked servers were migrated as well. But some Jobs are failing in the new server and working perfectly fine in the old server. One of the failing Jobs is a Job that fetches data from a Linked Server Oracle Oledb and inserts into our Tables. The Job has several steps and it fails in the first step after it does a couple of commands.
In the job history the error looks like this:
Executed as user: NT SERVICE\SQLSERVERAGENT
Insert into #Ourtemptable [SQLSTATE 01000] (Message 0)
Remove duplicates due to Sample table [SQLSTATE 01000] (Message 0)
Delete from TableA [SQLSTATE 01000] (Message 0)
Insert into TableA [SQLSTATE 01000] (Message 0)
Insert into #TemptableB [SQLSTATE 01000] (Message 0)
Remove duplicates due to Sample table [SQLSTATE 01000] (Message 0)
Insert into #TemptableC [SQLSTATE 01000] (Message 0)
Remove duplicates due to Sample table [SQLSTATE 01000] (Message 0)
Delete from Table C [SQLSTATE 01000] (Message 0)
Insert into Table4 [SQLSTATE 01000] (Message 0)
Delete from Table5 [SQLSTATE 01000] (Message 0)
The OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_SERVER_NAME" reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "CENTURY_SERVER". [SQLSTATE 42000] (Error 7330). The step failed.
So apparently the job is running fine up to this point "Delete from Table5 [SQLSTATE 01000] (Message 0) and then generates an error. " and then can not fetch data. The next step after Deleting from Table5 is Executing a @SQLStatement parameter that fetches from Linked server and inserts into one of the SQL tables.
I am super new to SQL and might not be explaining well. Forgive my unexperienced-ness on this.
Anyone had the same or similar issue? Please help.