Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 7
Newest
 Commentator
Impact
~3k people reached

  • 0 posts edited
  • 0 helpful flags
  • 2 votes cast
May
11
comment Database Default Locations doesn't persist
Marking yours as the answer as your comment prompted me to reboot, although I would prefer not to use the registry.
May
11
accepted Database Default Locations doesn't persist
May
11
comment Database Default Locations doesn't persist
Got it - needed to do a reboot. I had tried stopping & restarting the service via Services, but that didn't seem to work. I didn't change the settings in the registry, though - just used the normal SSMS setting and then rebooted.
May
11
awarded  Commentator
May
11
comment Database Default Locations doesn't persist
I don't even have those keys in the registry....
May
11
comment Database Default Locations doesn't persist
Hmm. Nope. Granted MSSQLSERVER access to the disk, so I could at least find the backups on the disk, but the default location changes still don't persist.
May
11
comment Database Default Locations doesn't persist
Same result - doesn't persist. I suspect it's because the account doesn't have permission to that drive. Need to find how which account it is and try give it access.
May
11
asked Database Default Locations doesn't persist
Apr
20
awarded  Notable Question
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?