143 reputation
6
bio website
location Cape Town, South Africa
age
visits member for 2 years, 5 months
seen Sep 1 at 18:36

Jan
15
awarded  Popular Question
Aug
28
answered Error Authenticating Proxy Account when Executing SSIS job
Jan
11
awarded  Scholar
Jan
11
accepted Remote queries and linked servers
Jan
11
comment Remote queries and linked servers
I'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.
Jan
11
comment Remote queries and linked servers
Thanks 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.
Jan
11
comment Remote queries and linked servers
That'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!
Jan
11
awarded  Supporter
Jan
9
comment 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).
Jan
9
revised Remote queries and linked servers
added 1 characters in body
Jan
9
comment Remote queries and linked servers
In 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?
Jan
9
comment Remote queries and linked servers
Thanks @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.
Jan
9
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.
Jan
9
awarded  Student
Jan
9
awarded  Editor
Jan
9
revised Remote queries and linked servers
edited body
Jan
9
asked Remote queries and linked servers
Jun
29
awarded  Teacher
Jun
27
answered SQL and Multiple Indexes