There are at more than a half dozen I have tried and I keep coming back to AdeptSQL myself.

ApexSQL Diff - http://www.apexsql.com

AdeptSQL Diff - http://www.adeptsql.com

SQLEffects Clarity -

To expand you can do this to get all on a server.

sp_msforeachdb 'select ''?'' dbname, convert(sysname,DatabasePropertyEx(''?'',''Recovery'')) recovery'

Please post the DDL of the Temp tables in full and please explain how this code is used, is in a stored procedure?

The difficult part is not knowing your intended design and whether or not you have gone far enough into the thought process. Have you sat down and done a paper...

Need more info. Please post the DDL of the Temp tables in full and please explain how this code is used, is in a stored procedure?

Lastly is that the condition...

C drive full up as well can get you and is commonly over looked.

If the previous does not solve the issue try setting up profiler to watch the activity while you reproduce the issue and review what is happening.

BOL states user which assumes groups cannot be used. sp_ChangeDBOwner changes the actual ownership between people. If you need the Group to have DBO type rights you assign them to...

After you table name add

WITH (INDEX(0))

This will force a table scan or clustered index scna (if you have one) which is what you are trying to accomplish.

You might also try...

That is the fun part.

With this

(Scenario 1)

JOIN ON (a.FieldID = b.FieldID AND b.Sold=1)

VS...

What I believe happens in a non-static call such as this is that all 750,000 records are pulled across then filtered so you loose the advantage of all the indexes...

You missed my other question:

How many rows are in remoteserver.database.dbo.table2 (the sql 7 server)?

BTW do you have Distributed Transaction Coordinator runnin or not? 

Dumb question but how are you referencing the server for TCP/IP. Is it by the name or by a DNS entry or the IP?

Have you tried using the IP itself...

How many rows are in remoteserver.database.dbo.table2 (the sql 7 server)?