Are you setting this up with the GUI, or are you scripting it out?

Can you post the SQL you are using if you are scripting it out?

It's just granting some security. It doesn't call those objects, unless SQL calls them behind the scenes. And the user I'm running it as is both an admin...

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

I had that same problem with SQLSafe a few years back too.

You might want to try disabling it for a couple days and just shipping SQL backups to a different server just to rule it out. We've had some funny...

Do you have any 3rd party backup/audit/etc. tools running on that box (Idera, RedGate, Apex, TSM, etc)?

I really am not interested in improving the speed of the query...it can take 20 min. to run for all I care. I'm just trying to figure out why...

That query is basically creating a denormalized table on the branch servers from corporate data to be consumed. It needs to be current, as it's information about people making...

Nicholas Cain (9/25/2008)

---

Have you monitored the locks and lock escalations while the query is running?

Have you performed a reindex recently and are the stats up to date?

How does the execution...

I thought when you had the collation compatible setting turned on it was supposed to do the joins on the remote server?

And in any case, I don't care...

Yep.

Nicholas Cain (9/25/2008)

---

I'm trying to figure exactly what you are doing here. You'll have to excuse me, sometimes the ADD kicks in and I miss things.

Just to try and clarify...

I've tried that, with basically the same script. (I set the collation compatible option to true also). It didn't make a difference.

That's a good point...even if...

I tried all day to reproduce the error, by running the exact SQL that the SQL job runs in a query window and can't. I have also tried running...

Interesting. I'd never heard of the REMOTE join hint.

I just tested it, and the speed of the query didn't change much.

Then something occured to me...when I run this...