Have you tried using client provider 10 to connect to your 2008 sql server?

That could be the problem.

Agree with Thom about the "from" statement, and also the replace and LEN calculation may be costly.

Important factors are:  size of the tables, appropriate indexes, take advantage of common table...

Agree with Fredrico, please post and explain the plan.

I have run your script and this is the result I got, so, not sure how you got that error.

2019-01-31 00:00:00.007

have you tried transparent gateway between oracle and sql server?

The security is fine, as mentioned, it is a shared functional data-source.  over 100s of reports are using that.

New Findings:
about 20 minutes ago, created a linked...

The only thing left for me to believe is the possibility of a bug(s) in the system.

It is not making sense why the history is written out when SSIS...

That's very good question and yes I have dropped and recreated the non-conforming job.

Also, yes, I have checked and made sure that no other process is deleting, 1 because...

there are only 3 record and the table is no where near full

see previous snap-shots from history results.

see attached for more interesting info on the "agent" side.

beginning...

see attached

I thought this setting were universal / global for all jobs... as seen in previous message, 1 works and the other does not ..

more info...

stopped them from running as scheduled.

run them 2 jobs manually (see attached) one of them has written out to the history table and one did not...

no entries in history table

the jobs are simple: getting record(s) from a source and writing to another table.

There is no history record(s) for successful nor failure

I may...

Pete,

You already, probably, have done this but had to share that with you anyway.

There are times that the queues go down in case of some error what not.....

Pete,

BTW:: I have all my objects pertaining this whole event notification process in my MSDB.. this way I don't have to worry about the permissions and restrictions.

just...

I went ahead and created a table in one of the databases and since the USP I attached earlier looks over the server then generated a notification right away and...