Remote queries and linked serversI'm actually going to mark this as the answer, even though it didn't get what I wanted (a fix to the query plan / permissions issue). But having the data on the same machine seems to be the only proper solution.

Jan11

comment

Remote queries and linked serversThanks Brian. The queries themselves aren't too complex. I really wanted to sort the privileges out - seemed like the easiest answer. But even with sysadmin rights (the remote user), it wouldn't work - even for simple join queries. I am starting to doubt that it's a valid fix. I think I'm going to try move all the data across, then move it back, etc. Like you say, having the data on the same machine seems the best option.

Jan11

comment

Remote queries and linked serversThat's an amazingly detailed answer, thanks very much. I don't have time to test/implement it, unfortunately, because of deadlines and because I'm not sure it's appropriate: the job does a LOT more than the above example query, using cursors, bulk updates, individual inserts & updates, all between the two servers, so to rewrite all that into this approach, seems like too big a task. Thanks for taking the time to answer, though - one day when I'm big I'll have a look back at what this actually does!

Remote queries and linked servers@Zane - I don't think it's actually relevant that it's in an SSIS package, as I get the same slow results when I run the query in Management Studio. But for what it's worth, the package uses an Execute SQL Task on the local db (INT), calling a proc that uses the query as above. The package runs under a proxy account. But as I say, same results in SSMS, with my own login (sysadmin).

Remote queries and linked serversIn the link I provided above, it says the reason is just a permission issue. So I'm quite keen to explore that - if I can get the permissions right, it should work without any code changes. But I've given the linked server login sysadmin & ddladmin roles, but it's not helping. Has anyone actually fixed this issue with the right permissions?

Jan9

comment

Remote queries and linked serversThanks @edq. The query is just a simple, single example. There are lots more nested selects & updates - some with joins to multiple tables on both servers. So I don't think OPENQUERY would work.

Jan9

comment

Remote queries and linked servers@billinkc - yes, i've (over)simplified the query. There are lots of queries that join tables on both servers. The example I gave is a valid example - it's part of some processing in a cursor loop, where the rest of the processing includes tables on both servers. I included this specific example because it's one of the simplest queries - I would have expected it to run entirely on the remote server. Instead, it seems it gets ALL the ids & secondary_ids from the remote server, then filters on the local server, then runs the update. That seems to create the network traffic & delay.