network profile
| bio | website | |
|---|---|---|
| location | Cape Town, South Africa | |
| age | ||
| visits | member for | 10 months |
| seen | Jan 11 at 19:06 | |
| stats | profile views | 1 |
|
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 |
