sp_help_targetserver (Transact-SQL)


THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Lists all target servers.

Topic link icon Transact-SQL Syntax Conventions

     [ [ @server_name = ] 'server_name' ]  

[ @server_name= ] 'server_name'
The name of the server for which to return information. server_name is nvarchar(30), with a default of NULL.

0 (success) or 1 (failure)

If server_name is not specified, sp_help_targetserver returns this result set.

Column nameData typeDescription
server_idintServer identification number.
server_namenvarchar(30)Server name.
locationnvarchar(200)Location of the specified server.
time_zone_adjustmentintTime zone adjustment, in hours, from Greenwich mean time (GMT).
enlist_datedatetimeDate of the specified server's enlistment.
last_poll_datedatetimeDate the server was last polled for jobs.
statusintStatus of the specified server.
unread_instructionsintWhether the server has unread instructions. If all rows have been downloaded, this column is 0.
local_timedatetimeLocal date and time on the target server, which is based on the local time on the target server as of the last poll of the master server.
enlisted_by_nt_usernvarchar(100)Microsoft Windows user that enlisted the target server.
poll_intervalintFrequency in seconds with which the target server polls the Master SQLServerAgent service in order to download jobs and upload job status.

To execute this stored procedure, a user must be a member of the sysadmin fixed server role.

A. Listing information for all registered target servers

The following example lists information for all registered target servers.

USE msdb ;  
EXEC dbo.sp_help_targetserver ;  

B. Listing information for a specific target server

The following example lists information for the target server SEATTLE2.

USE msdb ;  
EXEC dbo.sp_help_targetserver N'SEATTLE2' ;  

sp_add_targetservergroup (Transact-SQL)
sp_delete_targetserver (Transact-SQL)
sp_delete_targetservergroup (Transact-SQL)
sp_update_targetservergroup (Transact-SQL)
dbo.sysdownloadlist (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions