CODEWITHSUNDEEP
sql-serversql-server-2008
ASH
ASH

Reputation: 20302

Auto-Pull Data from Remote Server

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.

Upvotes: 0

Views: 242

Answers (1)

FrT
FrT

Reputation: 26

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!

Upvotes: 1

Related Questions