View from another server

[lexi0088]

I am trying to move data from one server to another, but have hit a bump on how to replicate a view I use on the old server. Due to the traffic on my old server, I had to create an independent server to hold a few tables in which 2 seperate programs use almost independantly. My only problem is that I have a view in my old server that combines several tables which will not be in my new server. In order to optimize performance (and not tie up my old server), I would like to somehow replicate this view only on a nightly basis into my new server... since real-time data is not necessary. Can anyone give me specific instructions on the best way to do this? I use sql all of the time on querying and changing data, but do not have much experience with this type of administration.

 

[MartinCroft]

Why does the view need changing? Does the structure of the view change, not following why you need to refresh unless the structure of the view changes. Do you actually mean view or the underlying tables of the view? What version of SQL are you using ?

Sorry lots of questions but help clarify what you want to achieve

 

[SQLWilts]

Hi,
If I have understood you correctly, you want to see the data that the view makes available.
To use "real time" data, you could use linked servers (google how to set that up). Then you could query as in

select * from oldserver.databasename.owner.view where foo = 'bar'

or, if you want the data held in a flat table in the new server, and disk space isnt an issue (depending on the quantity of data the view makes available) then you could creat a table (#table or permanent one), populate if from your view and push that from your old server to your new one, in which case you would have to create your linked server on your old server pointing at your new one.
I hope that this has given you a few ideas.

 

[lexi0088]

My goal is to have the data that the view supplies available NOT in real time. When my view is called, it ties up the old server, making everything that runs on that server slow down. I would like to somehow take a snapshot of the tables in the view (or the the view itself) on a nightly basis and copy this into the new server. Having a linked server does not solve my issues. I am running SQL 2005

 

[SQLWilts]

Lexi,
You may well need the linked server to TRANSFER the data to your new server using select or to create a surrogate temp table (select into from your view) and then use SSIS to get it across to your new server.
You then select from this new table that you have transfered over night