I am not sure what the best design pattern for this problem is so any suggestions would be greatly appreciated. I have two SQL Servers A (with DBs P,Q,R) and B (with DBs X,Y,Z).
What is an efficient way to do a join of tables situated on Server A with those on Server B from command-line? Basically, I am trying to automate a long running list of queries and am not really sure how to proceed.
EDIT: I do not have control over the servers and am not an admin.
At a minimum I would create a Link Server between the two servers. Using something like OPENROWSET will be slower than using a Link Server.
Depending on how fast you need these queries to run you might consider having a nightly process that copies the data from Server A to Server b (or the other way around) so that you don't need to worry about cross server.