In the abscene of more information, I am assuming that the question is that for an INSERT statement: what would be better if you have a view that is based...

You might be running into the issues of proportional fill.  This is very well documented by Mat Stephen on his blog:

http://blogs.technet.com/mat_stephen/archive/2005/02/02/365370.aspx

Also, check and make sure that tempdb is not...

You can do the join on the remote server (including any filters that you want to apply to the data set) and get back only the data that you need. ...

The comments are in-between the script itself and are self-explanatory hence I did not elaborate on them in text assuming that the user is familiar with sql and the functions...

Please re-read the original question:

1) The sql statement is totally dynamic, it's not static, the search criteria can be totally different, the joining tables could be totally different....in order to...

The problem in this case though is that these are MSDE databases and is being used for the caching of some of the tables on the web server....I have configured...

And the reason why it will not return an error message in this case is that at compile time itself, MSDTC notices that the destination server does not exist and...

Patti:

The script has an error....I was using SetMessages as my stored proc for having my custom error messages. You can remove that piece of code from the first proc...