As always, you'll have to compare execution plans and runtimes to build your choice on. Hey, this may even by the occasion for a 'aha-erlebnis'

Hey, I thought the were bits and pieces of bricks and cement with your last reply

As far as I'm concerned you may as well...

Perform it on both, maybe that gets it all into the daylight, but at least perform it where you are having the problem (i.e. the select).

- QA\Query\display estimated execution plan...

read my previous reply

Activate the "show (estimated) execution plan" in QueryAnalyser, maybe that will clarify the issue.

Do you have an index on column Code ?

If not it will have to perform a table-scan and therefor it will not be able to read through the uncommitted row(s).

If the relation between the temp-tb and the tb is 1-1 and you're checking existance on the unique key, supporting it with a clustered index might speed up this join-operation.

With...

And that are just the adjustments you need to rename the tables.

(You coud script your db and perform the rename textwize and recreate it...

you could also use tools like CA-ERwin,...

In addition to Ray's addition   :

Having more Deadlocks lately ?

I guess that should be a right join because the exists-function is done using the  tblMMStudentTestScores.

But because I prefer using Left joins (I find it smoothly to read) ...

SELECT ...

FROM #tblMMStudentTestScoresTEMP...

Can you check Microsoft Knowledge Base Article - 264012 :

PRB: Connectivity Issue with MDAC and Oracle8i

You might have to add some reg-keys, mentioned in the article.

Maybe defeat it with it's own weapons ?

use master

select object_id('sp_schedule_status_reset') as The_Nasty_One

just my 2ct.

- did you configure the mail-profile when logged in as sql-agent-service-account ?

- does your sql-agent-service-account has the needed authorities for that email-profile ?

- Is it working when you...

When I install new basic software (Oracle client in this case) I alway reboot.

Especialy when installing on a server. This way I can better relate a problem to a last...

Indeed

I suppose your sqlserver-agent-account is member of windows-server-administrators group ?

If not, you'll need to give it access to the Oracle-folders and reg-keys.

You might also want to login on the server...