May 16, 2012

sp_MShelpmergedynamicsnapshotjob (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_MShelpmergedynamicsnapshotjob(nvarchar @publication
, nvarchar @dynamic_snapshot_jobname
, uniqueidentifier @dynamic_snapshot_jobid)

MetaData:

 --   
-- Name: sp_MShelpmergedynamicsnapshotjob
--
-- Description: This procedure returns a listing of dynamic snapshot jobs.
--
-- Parameters: @publication sysname (optional, default '%'): When @publication
-- is '%', all dynamic snapshot jobs with the matching
-- @dynamic_snapshot_jobid and @dynamic_snapshot_jobname will be
-- returned.
-- @dynamic_snapshot_jobname sysname (optional, default '%'): When
-- @dynamic_snapshot_jobname is '%', all dynamic snapshot jobs that
-- belong to @publication with the matching @dynamic_snapshot_jobid
-- will be returned.
-- @dynamic_snapshot_jobid (optional, default null): When
-- @dynamic_snapshot_jobid is null, all dynamic snapshot jobs
-- that belong to @publication with the matching
-- @dynamic_snapshot_jobname will be returned.
--
-- Notes: If all parameters are left unspecified when this procedure is called,
-- all dynamic snapshot jobs for the current database will be returned.
--
-- Result: id int
-- job_name sysname
-- job_id uniqueidentifier -- job id of the dynamic snapshot job
-- dynamic_filter_login sysname
-- dynamic_filter_hostname sysname
-- dynamic_snapshot_location nvarchar(255)
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Execute permission of this stored procedure is granted to public
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MShelpmergedynamicsnapshotjob (
@publication sysname = N'%',
@dynamic_snapshot_jobname sysname = N'%',
@dynamic_snapshot_jobid uniqueidentifier = null
)
as
begin
set nocount on
declare @retcode int

exec @retcode = sys.sp_MSreplcheck_publish
if @@error<>0 or @retcode<>0
return 1

if object_id('sysmergepublications') is NULL
begin
return 0
end

select 'id' = j.id,
'job_name' = j.name,
'job_id' = j.job_id,
'suser_sname' = j.dynamic_filter_login,
'host_name' = j.dynamic_filter_hostname,
'dynamic_snapshot_location' = j.dynamic_snapshot_location
from dbo.sysmergepublications p
inner join MSdynamicsnapshotjobs j
on p.pubid = j.pubid
where (p.name = @publication or @publication = N'%')
and (j.name = @dynamic_snapshot_jobname or @dynamic_snapshot_jobname = N'%')
and (j.job_id = @dynamic_snapshot_jobid or @dynamic_snapshot_jobid is null)

if @@error <> 0
return (1)
else
return (0)

end

No comments:

Post a Comment

Total Pageviews