Job Failing

Question

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.

Answer 1

Hi @Noz Mamur ,

Please enable "Allow in Process" option for the provider
In SQL Server, click Server Object, opening up linked server > providers > right-click on OraOLEDB.Oracle and click properties, checking on "Allow in Process"

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

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Errors involving linked servers can be difficult to troubleshoot, and even more so when the linked server is not SQL Server, as you often need knowledge on both sides.

Since this job was working before the migration, what comes close at hand is that there is some piece of configuration that was forgotten. Which piece - please don't ask me. But if the old server is still around, check the contents in sys.servers, to see if there is any difference.

Also try running the operation directly from SSMS to see if this works, or at least you get some more error information.

Answer 3

• UPDATE So I tried running the queries in SSMS query window and it failed with the same exact error. I know there is no issue with the query. Then I tried to Select top 1000 and Insert INTO and it did in fact worked perfectly fine. With all the Case and Where conditions it inserted 84 rows into my SQL Table. But when tried to run the entire query again removing TOP 1000 it failed with this error again. I have no idea...

Answer 4

Seeing how the query looks like, I would suggest that you rewrite the operation to receive the data from OPENQUERY into a temp table, without any filtering against ACTIVECORE_ORDERS. Then insert into the AC_NOTES from temp table applying that filtering.

Also, the filtering on NOTE_TYPE and NOTE_LEVEL is better to pushed inside OPENQUERY, so that is performed on the Oracle side, reducing the number of rows that are brought over.

When working with linked servers, it often pays off to be simple-minded.