CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008sql-server-agent
CptSupermrkt
CptSupermrkt

Reputation: 7134

Accessing another SQL Server from SQL Job Agent

I have a SQL Server Agent Job on "server X." This job is simple, and uses the following query to refresh a table (on server X) by clearing it, then re-populating it with data from a view (also on server X):

DELETE FROM [ClientList].[dbo].[LatestDownloadLogs]

INSERT INTO [ClientList].[dbo].[LatestDownloadLogs] 
SELECT * FROM [ClientList].[dbo].[latestoverview-union]

The "LatestDownloadLogs" table is moving to "server Y," but the "latestoverview-union" view will remain on "server X".

Therefore what I need is something that looks like this:

DELETE FROM [server Y].[ClientList].[dbo].[LatestDownloadLogs]

INSERT INTO [server Y].[ClientList].[dbo].[LatestDownloadLogs] 
SELECT * FROM [server X].[ClientList].[dbo].[latestoverview-union]

Of course, it's not that easy, but hopefully that illustrates what I'm trying to accomplish.

Upvotes: 0

Views: 1523

Answers (2)

brian
brian

Reputation: 3695

Create a linked server on server x to server y.

Upvotes: 2

Steve Stedman
Steve Stedman

Reputation: 2672

You could use OPENROWSET, which'll require the connection info, username & password...

But first you may need to turn on Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1  
reconfigure  

EXEC sp_configure 'Ad Hoc Distributed Queries', 1  
reconfigure

You can then select, insert or delete

SELECT FROM
OPENROWSET (... params...)




UPDATE
OPENROWSET (... params...)

Hope this helps... good luck.

Upvotes: 0

Related Questions