Can I query a different server in a SQL Server Agent job?

Question

When creating a T-SQL step in a SQL Server Agent job, is there a way to pull from a database on a different server? For example, I'd like to query different database job histories across all of the various servers. Possible?

Answer 1

Besides running queries via linked servers there is also the whole 'multiserver' concept in SQL Agent. See:

• Managing Jobs Across an Enterprise
• Automating Administration Across an Enterprise

You should also look into Centralised Managed Servers in SSMS:

• Administering Multiple Servers Using Central Management Servers
• Application and Multi-Server Management (SQL 2008 R2 only)

Answer 2

You can only access an external server if it is a linked server in the server that you are running the query from. After adding the server as a linked server access objects on that server with the full name -- [servername].[database].[dbo].[object].

Answer 3

You can issue queries against linked servers with the OPENQUERY function. See here.

If you'd rather do it without a linked server, with an ad-hoc connection (specifying the connect string in the function call), use OPENROWSET. See here.