May 14, 2012

sp_MSenumdistributionagentproperties (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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_MSenumdistributionagentproperties(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, bit @show_security)

MetaData:

 CREATE PROCEDURE sys.sp_MSenumdistributionagentproperties   
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@show_security bit = 0
)
AS
begin
DECLARE @independent_agent bit
DECLARE @publisher_id int -- Server id of the Publisher with respect to the Distributor

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end

SELECT @publisher_id = NULL
SELECT @publisher_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(@publisher) = UPPER(srvname)

IF @publisher_id IS NULL
BEGIN
RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)
END

IF @publication <> '%' -- A publication name is specified
BEGIN
-- Determine whether the given publication shares an
-- agent with other publicaitons in the same database
-- or not
SELECT @independent_agent = 0
SELECT @independent_agent = independent_agent
FROM dbo.MSpublications
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND publication = @publication

IF @independent_agent IS NULL
BEGIN
RAISERROR(20026, 16, -1, @publication)
END

IF @independent_agent = 0
BEGIN
-- If the publication shares an agent with
-- other publications, change the publication
-- name to 'ALL' before querying the MSdistribution_agents
-- table
SELECT @publication = N'ALL'
END
END

-- These are the main differences between @show_security = 0 and 1:
--
-- @show_security = 0
-- This is the backcompat case and is really meant for 8.0
-- publishers. Setting it to this value returns the following
-- column values:
--
-- job_id, offload_enabled, offload_server, dts_package_name,
-- dts_package_location, status
--
-- Also note that the job_id returned here is from MSdistribution_agents
-- and NOT from sysjobs... so it is possible that the job has been
-- deleted via komodo procs but we still return the old job_id
--
-- @show_security = 1
-- This is the Yukon case returns the same columns as in @show_security = 0
-- but also adds the following columns to the resultset:
--
-- subscriber_security_mode, subscriber_login, credential_identity
-- distribution_agent_name, subscriber_type, subscriber_provider,
-- subscriber_datasource, subscriber_providerstring, subscriber_location,
-- subscriber_catalog
--
-- Also note that the job_id returned here is from sysjobs and not
-- from MSdistribution_agents. So if the job is deleted via the komodo
-- procedures then we will return NULL for the job_id
IF @show_security = 1
BEGIN
SELECT sj.job_id, msda.offload_enabled, msda.offload_server,
msda.dts_package_name, msda.dts_package_location,
-- status of this agent, only useful in detecting
-- whether or not the agent has be deactivated by cleanup
-- If so, all the rows in dbo.MSsubscriptions will be 0
(select top 1 s.status
from dbo.MSsubscriptions s where
s.agent_id = msda.id),
msda.subscriber_security_mode,
msda.subscriber_login,
sc.credential_identity,
msda.name,
msda.subscriber_type,
msda.subscriber_provider,
msda.subscriber_datasrc,
msda.subscriber_provider_string,
msda.subscriber_location,
msda.subscriber_catalog
FROM MSdistribution_agents msda
LEFT JOIN msdb..sysjobs sj
ON msda.job_id = sj.job_id
LEFT JOIN msdb..sysjobsteps sjs
ON msda.job_step_uid = sjs.step_uid
LEFT JOIN msdb..sysproxies sp
ON sjs.proxy_id = sp.proxy_id
LEFT JOIN sys.credentials sc
ON sp.credential_id = sc.credential_id
WHERE msda.publisher_id = @publisher_id
AND msda.publisher_db = @publisher_db
AND msda.publication LIKE @publication
END
ELSE
BEGIN
SELECT msda.job_id, msda.offload_enabled, msda.offload_server,
msda.dts_package_name, msda.dts_package_location,
-- status of this agent, only useful in detecting
-- whether or not the agent has be deactivated by cleanup
-- If so, all the rows in dbo.MSsubscriptions will be 0
(select top 1 s.status from dbo.MSsubscriptions s where
s.agent_id = msda.id)
FROM MSdistribution_agents msda
LEFT JOIN msdb..sysjobs sj
ON msda.job_id = sj.job_id
WHERE msda.publisher_id = @publisher_id
AND msda.publisher_db = @publisher_db
AND msda.publication LIKE @publication
END
end

No comments:

Post a Comment

Total Pageviews