Solved

MS SQL 2008 - Transfering data to external remote SQL

Posted on 2011-09-14
10
356 Views
Last Modified: 2012-05-12
Here is what I am try to do.

SQL1 - run a procedure that will first of all delete all records on a remote SQL2 table
 then update from SQL1 to the remote SQL2 table.
I have tried using ODBC with no success... I would like to have something like below...

INSERT INTO SQL2(column1, column2)
FROM SQL1(column1, column2)

My problem is my remote sql is the folloing. SQLB33.webcontrolcenter.com
and my sql database is SQL2  with table user

Thanks.
0
Comment
Question by:rbm1tch3ll
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 9

Accepted Solution

by:
DrewKjell earned 500 total points
ID: 36538034
Create a linked server on SQL2 to SQL1, then you can run an queries on SQL2 that are querying SQL1.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Drew
0
 
LVL 79

Expert Comment

by:arnold
ID: 36538781
Are you looking to maintain all sql server synchronized?
transactional merge?
log shipping, database mirroring?

0
 

Author Comment

by:rbm1tch3ll
ID: 36539057
No just update the remote server table once a week
0
Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

 
LVL 79

Expert Comment

by:arnold
ID: 36540050
IS the remote table going to be queried or can that database by in recovery mode to which you can apply incremental/transactional backups?
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36542840
Is the table schema on the local server constant or could it change during the week?
0
 

Author Comment

by:rbm1tch3ll
ID: 36542895
The table only needs ti be updated once a week.  What I am looking for is a stored procedure that will allow me to do this.  I am doing it manually niw
0
 
LVL 79

Expert Comment

by:arnold
ID: 36543050
BCP might be what you are looking for:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Are the two servers connected?
Did you try the linked database option recommended earlier?
0
 

Author Comment

by:rbm1tch3ll
ID: 36543075
No the servers arenot connected, to prevent access to internal database we have a hosting company that is hosting reporting data.  That is why I need to do thia
0
 
LVL 79

Expert Comment

by:arnold
ID: 36543134
What type of access do you have from the current sql server to the hosted one?
Do you FTP the data to that location?
You can link the database
i.e. on the internal server you will create a linked object that will be able to make alterations to the database/table on the remote side while the remote Side will not have a way to connect into the internal database.
A linked database is a one way street.

Your internal Database
linked object
Link_to_remote_database
running the following on the local system will let you pull information from the remote one.
select * from localserver.dbo.link_to_remote_database.table
 
Once this link is present you can create a sp on the local/internal database to generate queries and push the data into the remote database via the linked mechanism.

You could use VB to script what you currently manually do.
Export data from the table.
ftp/transfer the data file to the remote system.
Then on the remote system you will have to have a monitoring process to see whether the file has been uploaded, and if it has clear the existing table and import the new data.
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36598846
The other process you could try since it appears you are only interested in updating one table is scheduling an export of data from SQL1, most likely not the entire table, just the changes from the last week to a flat file of some type.  FTP the flat file to the hosting provider and have an import job scheduled to insert the data into SQL2.

The export and import could be built using SSIS or a script.  However is most comfortable for you.

Let me know if this helps or if I can clarify.

Drew
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

In this article we will discuss some EI Capitan Mail app issues and provide some manual process to resolve them.
In this article we have discussed about the OS X EI Capitan and how to fix Wi-Fi issue in OS X El Capitan. We have explained how to delete system level preferences and create a new Wi-Fi location to resolve Wi-Fi issue.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
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…

623 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