Solved

Copy data from a MS SQL View to a table on another SQL server

Posted on 2009-06-27
5
359 Views
Last Modified: 2012-05-07
Hi,
I have a view on one MS SQL server that I want to be copied to a table on another MS SQL server once a day. I'm not sure whether to use replciation or not. When I did is required me to replicate all the tables that the view referenced which is not what I want.
Any help much appreciated.
Thanks
0
Comment
Question by:browsw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24727524
check for OpenRowset in the BOL (books on line) to see if you can use it. You could then schedule a query to run once a day.
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24729027
You can do that by creating a linked server by using sp_addlinkedserver stored proc and then create the view as you would normally do.
Here is reference to the stored proc: http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx
Or, if you do not want to use the stored proc you can do that via GUI. In SSMS connect to the sql server>Expand "Server Objects> Right click "Linked Servers">select "New Linked Server".
Hope this helps.
P.
0
 

Author Closing Comment

by:browsw
ID: 31597534
Question not answered
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question