0

I am trying to get my local SQL server to pull data from another table on another server (remote server). I can manually click on the server and dump the data into my local server, but I can't figure out a way to schedule the job to run using SQL Server Agent.

Basically, it should be setup like this, I think.

Select * into [Server2].[DB2].[dbo].[PRFT_LOSS]
from [Server1].[DB1].[dbo].[PRFT_LOSS]

But, again, when I set this up to run on the scheduler, the job fails. I could swear I did this a few years ago, at another company. Is this possible or not? If SQL Server Agent doesn't allow a user to contact a remote server, can I set this up as a batch job, and use Windows Task Scheduler to run the job? I am using SQL Server 2008. I want this task to be automated. Thanks.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • 1
    Do you have a linked server set up between server2 and server1? – dfundako Mar 20 '18 at 20:33
  • [Linked Server failing on SQL Server job](https://stackoverflow.com/questions/32477446/linked-server-failing-on-sql-server-job#32477901) – Lukasz Szozda Mar 20 '18 at 20:35
  • Yes, I'm using Windows Authentication to connect to the other server. So, it's not possible to do this? Is that what you are saying? – ASH Mar 20 '18 at 20:36
  • 1
    Use SSIS package to move data between two servers, then schedule a job to run the SSIS package. Avoid using LinkedServer for data moves. – M.Ali Mar 20 '18 at 21:26
  • Why not just create a synonym or a view? – Eric Brandt Mar 20 '18 at 23:12

1 Answers1

1

I would recomend that you take a look on SQL server replication. A short description of replication is that you configure one server as publisher and then one or many servers accts as subscribers. In the replication setup you can select tabels, views, stored procedures and functions that should be included in the replication.

The replication uses the sql agent for distributing the data, so it need to be working on the servers.

You can read further on replication here

This is one of SQL Servers most powerfull features.

Good luck!

FrT
  • 26
  • 4