May 11, 2012

sp_MSdynamicsnapshotjobexistsatdistributor (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_MSdynamicsnapshotjobexistsatdistributor(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @dynamic_filter_login
, nvarchar @dynamic_filter_hostname)

MetaData:

 --   
-- Name: sp_MSdynamicsnapshotjobexistsatdistributor
--
-- Description: Returns the dynamic snapshot job_id if it exists for the given
-- publisher and host.
--
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Only members of the 'sysadmin' server role and members of the
-- 'db_owner' database role at the distributor can call this
-- procedure. This procedure is intended to be called through
-- the distributor_admin remote login in the case where
-- the distributor is a different machine from the publisher.
--
create procedure sys.sp_MSdynamicsnapshotjobexistsatdistributor
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@dynamic_filter_login sysname,
@dynamic_filter_hostname sysname,
@dynamic_snapshot_jobid uniqueidentifier output
)
as
begin
set nocount on

declare @job_id uniqueidentifier
declare @publisher_id int

--
-- security check
-- only db_owner can execute this
--
if (is_srvrolemember('sysadmin') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSdynamicsnapshotjobexistsatdistributor', 'distribution')
return (1)
end

if @dynamic_filter_login is NULL and @dynamic_filter_hostname is NULL
begin
raiserror(20653, 16, -1)
return 1
end

SELECT @publisher_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(srvname) = UPPER(@publisher)
IF @publisher_id IS NULL
BEGIN
RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)
END

select @job_id = job_id
from MSsnapshot_agents
where publisher_id = @publisher_id and
publication = @publication and
publisher_db = @publisher_db and
((@dynamic_filter_login is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @dynamic_filter_login) and
((@dynamic_filter_hostname is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @dynamic_filter_hostname) and
publication_type = 2
if @job_id is NULL or
not exists (select 1 from msdb..sysjobs where job_id = @job_id)
select @dynamic_snapshot_jobid = null
else
select @dynamic_snapshot_jobid = @job_id

RETURN(0)
end

No comments:

Post a Comment

Total Pageviews