Solved

MS SQL 2008 - Transfering data to external remote SQL

Posted on 2011-09-14
10
334 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 76

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
 
LVL 76

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 76

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 76

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

A lot of new and distinct gadgets are making their appearance every other day. The latest gadget that has wooed the attention of all gadget lovers and non gadget lovers alike is the Smartwatch. This tiny gadget is capable of offering live access to …
Set up iPhone and iPad email signatures to always send in high-quality HTML with this step-by step guide.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now