SQLTeam.com | Weblogs | Forums

Get data from remote server with only read access and save to local server

I have only read access to a view of a database on a remote server and need to store that data on a local Express Instance where I have full control. Both servers are NOT linked to each other! How can I achieve this? Is it possible at all?


Read-Only Access ( Copy ... and then paste ) ... into Something .. Excel .. or anything

From Excel put into Local Express

Put = means = Lots of different Ways
.. Import/Export
.. BCP
.. PowerShell
.. API's ... Custom Or .Net

Third Party Tools
.. specifically for this purpose

PAID or Open Source ..

It will depend on how often you want to refresh the data - the process of refreshing the data (incremental - full) and the purpose of being able to change the data.

You can use SSDT and build an integration services package - that exports the data from the source and imports into the destination. Or - you can use Powershell and SqlBulkCopy to extract the data and import into the destination - or BCP the data out of the source and BCP the data into the destination - or other options are available.

OR using

SMO ( sql server manangement objects ) .. Please google search

hack into the database
.. access the data
.. and then using some SMO commands access you local
and put it there

Easiest is probably to use OPENROWSET to copy data locally (assuming it's not too large).

SELECT remote.*
INTO dbo.local_table_name
FROM OPENROWSET('SQLNCLI', 'Server=your_server_name;your_username;your_password', remotedbname.dbo.your_view_name) 
AS remote;
1 Like