Copying Data from one SQL Server Database to Another SQL Database on a Daily basis

I am trying to figure out what would be the best way to copy SQL database views from one database to another.

Both databases are hosted on two different servers, so will require firewall access between them. I will need to send the views of the data on a daily basis to the required database.

Copy table to a different database on a different SQL Server – Tried this example but not getting any luck.

Answer

It looks like the example you linked suggests using linked servers. That in my opinion is the best route when going server to server. But since you mentioned that you are having trouble with that you can try using an OpenRowSet.

I won’t bother to add my own examples here because others like this one are already great examples.

Of course you would still need to make sure you can connect to that server within the machine you are using. I am not sure how your set up is or what it would take.

In any case, whenever you decide what is best, use SQLAgent to schedule a daily job. This daily job can execute queries or stored procedures. So, you could create a stored procedure that declares an OpenRowSet and does whatever you need.