April 27, 2012

sp_helpmergepullsubscription (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_helpmergepullsubscription(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @subscription_type)

MetaData:

   
create procedure sys.sp_helpmergepullsubscription(
@publication sysname = '%', -- Publication name --
@publisher sysname = '%', -- Publisher server --
@publisher_db sysname = '%', -- Publication database --
@subscription_type nvarchar(10) = 'pull' -- Show only pull subscriptions --
)AS

SET NOCOUNT ON

--
-- Declarations.
--

declare @retcode int
declare @srvid int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
declare @cursor_open int
declare @subscriber sysname
declare @subscriber_db sysname
declare @publisher_local sysname
declare @publisher_db_local sysname
declare @publication_local sysname
declare @helpsubscriptioncursor_open int
declare @subtype1 smallint
declare @subtype2 smallint
declare @subtype3 smallint
declare @fpullsubexists bit

set @cursor_open = 0
select @publisher_db = RTRIM(@publisher_db)
select @publication = RTRIM(@publication)
select @fpullsubexists = 0

-- For attach
if exists (select * from sys.objects where name = 'MSrepl_restore_stage')
-- The database is attached from a subscription copy file without using
-- sp_attachsubscription. Return nothing
return 0

--
-- Calling sp_help* is all right whether current database is enabled for pub/sub or not
--

if object_id('sysmergesubscriptions', 'U') is null
begin
return 0
end

-- Security check --
EXEC @retcode = dbo.sp_MSreplcheck_subscribe
if @@ERROR <> 0 or @retcode <> 0
return(1)

set @subscriber = @@SERVERNAME
set @subscriber_db = DB_NAME()

--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is defined
--
IF @publisher <> '%' AND @publisher IS NOT NULL
BEGIN
EXECUTE @retcode = sys.sp_validname @publisher

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- Parameter Check: @publication.
-- If the publication name is specified, check to make sure that it
-- conforms to the rules for identifiers and that the publication
-- actually exists. Disallow NULL.
--
if @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_helpmergepullsubscription')
RETURN (1)
END

--
-- Parameter Check: @subscription_type.
-- Set subscription_typeid based on the @subscription_type specified.
--
-- subscription_type subscription_type
-- ================= ===============
-- 0 push
-- 1,2,3 pull
-- 0,1 both
--
if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull', 'both')
BEGIN
RAISERROR (14128, 16, -1)
RETURN (1)
END

IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
begin
set @subtype1 = 0
set @subtype2 = 1
set @subtype3 = 1
end
else IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
begin
set @subtype1 = 0
set @subtype2 = 0
set @subtype3 = 0
end
else
begin
-- including pull subscription and pull/anonymous/lightweight subscription
set @subtype1 = 1
set @subtype2 = 2
set @subtype3 = 3
end

IF object_id('MSsubscription_properties', 'U') is NULL
RETURN (0)

--
-- Get subscriptions
--

create table #helpmergepullsubscription
(
publication sysname collate database_default not null,
publisher sysname collate database_default not null,
publisher_db sysname collate database_default not null,
subscriber sysname collate database_default not null,
subscriber_db sysname collate database_default not null,
status int NOT NULL,
subscriber_type int NOT NULL,
subscription_type int NOT NULL,
priority float(8) NOT NULL,
sync_type tinyint NOT NULL,
description nvarchar(255) collate database_default null,
merge_jobid binary(16) NULL,
last_updated nvarchar(26) collate database_default null,
use_interactive_resolver int NULL,
subid uniqueidentifier not NULL,
last_sync_status int NULL,
last_sync_summary sysname collate database_default null
)

if object_id('sysmergesubscriptions', 'U') is not null
begin
IF @publisher IS NULL and @publisher_db IS NULL
BEGIN
-- show the loopback subscription
insert into #helpmergepullsubscription
select pubs.name, pubs.publisher, pubs.publisher_db, subs.subscriber_server, subs.db_name,
subs.status, subs.subscriber_type, subs.subscription_type, subs.priority,
subs.sync_type, subs.description, replinfo.merge_jobid,
sys.fn_replformatdatetime(subs.last_sync_date),
replinfo.use_interactive_resolver, subs.subid, subs.last_sync_status, subs.last_sync_summary
FROM sysmergesubscriptions subs,
MSmerge_replinfo replinfo,
sysmergepublications pubs
where subs.subid = subs.pubid
and pubs.pubid = subs.pubid
and replinfo.repid = subs.subid
END
else
begin
declare #cursor cursor local FAST_FORWARD FOR select DISTINCT sub.subid, sub.pubid
FROM dbo.sysmergesubscriptions sub,
dbo.sysmergepublications pub
WHERE ((@subscriber = N'%') OR (UPPER(sub.subscriber_server) = UPPER(@subscriber) collate database_default))
AND ((@publisher = N'%') OR (UPPER(pub.publisher) = UPPER(@publisher) collate database_default))
AND pub.name LIKE @publication
AND sub.pubid = pub.pubid
AND ((@subscriber_db = N'%') OR (sub.db_name = @subscriber_db collate database_default))
AND ((@publisher_db = N'%') OR (pub.publisher_db = @publisher_db collate database_default))
AND sub.subscription_type in (@subtype1, @subtype2, @subtype3)
FOR READ ONLY

open #cursor
select @cursor_open = 1
fetch next from #cursor into @subid, @pubid
while (@@fetch_status <> -1)
begin
insert into #helpmergepullsubscription
select pubs.name, pubs.publisher, pubs.publisher_db, subs.subscriber_server, subs.db_name,
subs.status, subs.subscriber_type, subs.subscription_type, subs.priority,
subs.sync_type, subs.description, replinfo.merge_jobid,
sys.fn_replformatdatetime(subs.last_sync_date),
replinfo.use_interactive_resolver, @subid, subs.last_sync_status, subs.last_sync_summary
FROM sysmergesubscriptions subs,
MSmerge_replinfo replinfo,
sysmergepublications pubs
where subs.subid = @subid
and pubs.pubid = @pubid
and subs.pubid = @pubid
and @subid <> @pubid -- do not show the loopback subscription
and replinfo.repid = subs.subid

if @@ERROR <> 0
begin
set @retcode = 1
goto DONE
end
fetch next from #cursor into @subid, @pubid
end
end
end

IF EXISTS(SELECT *
FROM sysmergesubscriptions
WHERE subscription_type != 0)
BEGIN
SELECT @fpullsubexists = 1
END

select 'subscription_name'= hs.publisher + ':' + hs.publisher_db + ':' + hs.publication collate database_default, hs.publication, hs.publisher,
hs.publisher_db, hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type, hs.subscription_type,
hs.priority, hs.sync_type, hs.description, hs.merge_jobid, cast(isnull(sp.enabled_for_syncmgr,0) as int) as enabled_for_syncmgr, hs.last_updated, sp.publisher_login,
sys.fn_repldecryptver4(sp.publisher_password), sp.publisher_security_mode, sp.distributor, sp.distributor_login,
sys.fn_repldecryptver4(sp.distributor_password), sp.distributor_security_mode, 'ftp_address' = null, 'ftp_port' = 0,
'ftp_login' = null, 'ftp_password' = null, sp.alt_snapshot_folder, sp.working_directory, sp.use_ftp,
sp.offload_agent, sp.offload_server, hs.use_interactive_resolver, hs.subid,
sp.dynamic_snapshot_location, hs.last_sync_status, hs.last_sync_summary, sp.use_web_sync,
sp.internet_url, sp.internet_login, sys.fn_repldecryptver4(sp.internet_password), sp.internet_security_mode,
sp.internet_timeout, sp.hostname,
'job_login' = sc.credential_identity,
'job_password' = '-- -- -- -- -- '
from #helpmergepullsubscription hs
left outer join MSsubscription_properties sp
on hs.publisher = sp.publisher collate database_default
and hs.publisher_db = sp.publisher_db collate database_default
and hs.publication = sp.publication collate database_default
left outer join msdb..sysjobsteps sj
on sp.job_step_uid = sj.step_uid
left outer join msdb..sysproxies p
on sj.proxy_id = p.proxy_id
left join sys.credentials sc
on p.credential_id = sc.credential_id
order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
if @@error <> 0 return 1

select @retcode = 0
DONE:
if (@cursor_open = 1)
begin
close #cursor
deallocate #cursor
end

return @retcode

No comments:

Post a Comment

Total Pageviews