Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL 2008 - Transfering data to external remote SQL

Posted on 2011-09-14
10
Medium Priority
?
360 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 2000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

704 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