Yes we do have weekly maintenance jobs but in this particular case, its completely a different scenario. The server in question is a "dedicated warehouse" build server used for monthly...

Resolved! Yes, it was indeed a case of index not being built before using the table. Rebuilding the indexes helped.

THANK YOU, Hugo & Gail for the suggestions!

Another thought that comes to mind is:

I understand there could be a problem with "ascending keys" in the tables that are loaded before being used in the SELECT query which...

Thanks for the suggestion of "ascending key problem" which I'm going to dig into.

But at this point, we don't mind using a sledgehammer approach. Also, there is no time...

Looks similar to what we had encounter with our servers when DTC was not enabled on a link server. Maybe this will help you with resolving the error:

http://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction