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

SSIS and Import/Export
1

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?

Martin

2

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

3

Third Party Tools
.. specifically for this purpose

PAID or Open Source ..

4

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.

5

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

6

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