Job Failing

Noz Mamur 21 Reputation points
2022-07-18T22:34:48.48+00:00

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.

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
568 questions
Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,146 Reputation points
    2022-07-19T03:12:48.303+00:00

    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.


  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-07-19T09:04:48.513+00:00

    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.


  3. Noz Mamur 21 Reputation points
    2022-07-20T13:17:54.263+00:00
    • 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...

  4. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-07-21T07:59:00.94+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.