The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_MSget_agent_names(nvarchar @publication, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher
, nvarchar @publisher_db)
MetaData:
create procedure sys.sp_MSget_agent_names ( @publication sysname, @subscriber sysname = NULL, @subscriber_db sysname = NULL, @publisher sysname = NULL, @publisher_db sysname = NULL ) as begin set nocount on declare @retcode int if @publisher is null begin -- -- We are at the publisher side -- declare @distributor sysname declare @distribdb sysname declare @distproc nvarchar(1000), @pubdb sysname -- -- do the publish security check -- exec @retcode = sp_MSreplcheck_publish if @retcode <> 0 or @@error <> 0 return 1 -- -- call into the distributor. Get distribution server information -- for remote RPC agent verification. -- select @pubdb = db_name() EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT, @distribdb = @distribdb OUTPUT IF @@error <> 0 OR @retcode <> 0 BEGIN RAISERROR (14071, 16, -1) RETURN (1) END -- -- Call proc to change the distributor -- select @publisher = publishingservername() SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.sys.sp_MSget_agent_names' exec @retcode = @distproc @publisher = @publisher, @publisher_db = @pubdb, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_db end else begin -- -- We are at the distribution db -- declare @publisher_id smallint -- -- Do the security check - should be db_owner() -- if (is_srvrolemember('sysadmin') <> 1 and is_member('db_owner') <> 1) begin raiserror(21050, 14, -1) return (1) end -- -- Get the publisher id -- select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default if @subscriber is null -- Returning publication agents if @subscriber is null begin declare @snapshot_agent sysname, @logreader_agent sysname, @qreader_agent sysname -- Have to use name in sysjobs table because users may rename -- the SQLServerAgent jobs. If users deleted the jobs, DMO scripting will -- create new jobs. select 'snapshot_agent' = (select j.name from MSsnapshot_agents a, msdb.dbo.sysjobs j where publisher_id = @publisher_id and publisher_db = @publisher_db collate database_default and publication = @publication collate database_default and a.job_id = j.job_id and a.dynamic_filter_login = NULL and a.dynamic_filter_hostname = NULL), 'logreader_agent' = (select j.name from MSlogreader_agents a, msdb.dbo.sysjobs j where publisher_id = @publisher_id and publisher_db = @publisher_db collate database_default and a.job_id = j.job_id), 'qreader_agent' = (select j.name from MSqreader_agents a, msdb.dbo.sysjobs j where a.job_id = j.job_id) end else begin declare @subscriber_id smallint select @subscriber_id = srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@subscriber) collate database_default declare @publication_type int, @independent_agent bit select @publication_type = publication_type, @independent_agent = independent_agent from MSpublications where publisher_id = @publisher_id and publisher_db = @publisher_db and publication = @publication if @publication_type = 2 -- Merge publication, get merge agent name begin select 'merge_agent' = j.name from dbo.MSmerge_agents a, msdb.dbo.sysjobs j where publisher_id = @publisher_id and publisher_db = @publisher_db collate database_default and publication = @publication collate database_default and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db collate database_default and a.job_id = j.job_id end else begin select 'distribution_agent' = j.name from MSdistribution_agents a, msdb.dbo.sysjobs j where publisher_id = @publisher_id and publisher_db = @publisher_db collate database_default and (publication = @publication collate database_default or publication = N'ALL' and @independent_agent = 0) and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db collate database_default and a.job_id = j.job_id end end end -- If @publisher is null -- -- all done -- end
No comments:
Post a Comment