Solved

MS SQL 2008 - Transfering data to external remote SQL

Posted on 2011-09-14
10
345 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
  • 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 77

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 77

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 77

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 77

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There is a security feature on iOS devices that is nearly impenetrable when it has been activated.  This article will provide some possible solutions as well as necessary steps to take to ensure you do not end up with a locked device.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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