MS SQL 2008 - Transfering data to external remote SQL

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.
rbm1tch3llAsked:
Who is Participating?
 
DrewKjellConnect With a Mentor Commented:
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
 
arnoldCommented:
Are you looking to maintain all sql server synchronized?
transactional merge?
log shipping, database mirroring?

0
 
rbm1tch3llAuthor Commented:
No just update the remote server table once a week
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
arnoldCommented:
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
 
DrewKjellCommented:
Is the table schema on the local server constant or could it change during the week?
0
 
rbm1tch3llAuthor Commented:
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
 
arnoldCommented:
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
 
rbm1tch3llAuthor Commented:
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
 
arnoldCommented:
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
 
DrewKjellCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.