-- Name:
-- sp_IHhelppublication
-- Description:
-- HREPL publication properties
-- Security:
-- Internal
-- Requires Certificate signature for catalog access
-- Returns:
-- Result set of publication properties
-- Owner:
--
CREATE PROCEDURE sys.sp_IHhelppublication
(
@publication sysname = '%',
@found int = 23456 OUTPUT, -- a flag indicate returning row
@publisher sysname,
@publisher_type sysname
)
AS
BEGIN
SET NOCOUNT ON
/*
** Declarations.
*/
DECLARE @pubid int
DECLARE @has_subscription bit
DECLARE @retcode int
DECLARE @no_row bit
DECLARE @publish_bit int
DECLARE @pubname sysname
DECLARE @username sysname
DECLARE @OPT_ENABLED_FOR_HET_SUB int
DECLARE @publication_ids TABLE
(
pubid int
)
SELECT @publish_bit = 1
SELECT @username = suser_sname()
SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_IHhelppublication')
RETURN (1)
END
/*
** Initializations.
*/
IF @found = 23456
BEGIN
SELECT @no_row=0
END
ELSE
BEGIN
SELECT @no_row=1
END
/*
** Parameter Check: @publication.
** Check to make sure that there are some publications
** to display.
*/
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_IHhelppublication')
RETURN (1)
END
IF @publication <> '%'
BEGIN
EXECUTE @retcode = dbo.sp_validname @publication
IF @retcode <> 0
RETURN (1)
END
-- Get list of matching publication id's for
-- this publisher/type combo
INSERT INTO @publication_ids
SELECT pubid
FROM sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)
IF NOT EXISTS
(
SELECT *
FROM @publication_ids
)
BEGIN
SELECT @found = 0
RETURN (0)
END
ELSE
BEGIN
SELECT @found = 1
IF @no_row <>0
BEGIN
RETURN(0)
END
END
/*
** Create a temp table of pubids identifying publications that the current user has access to
*/
CREATE TABLE #accessiblepubs (pubid int)
DECLARE hC CURSOR LOCAL FAST_FORWARD FOR
SELECT sp.pubid,
sp.name
FROM syspublications sp,
@publication_ids pi
WHERE ((sp.name = @publication) or (@publication = N'%'))
AND sp.pubid = pi.pubid
OPEN hC
FETCH hC INTO @pubid, @pubname
WHILE (@@fetch_status <> -1)
BEGIN
EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @pubname,
@raise_fatal_error = 0,
@given_login = @username,
@publisher = @publisher
IF (@retcode = 0 AND @@error = 0)
BEGIN
INSERT INTO #accessiblepubs values(@pubid)
END
FETCH hC INTO @pubid, @pubname
END
CLOSE hC
DEALLOCATE hC
/*
** Join the table of accessible pubids to the publication entries retrieved from syspublications
*/
SELECT 'pubid' = sp.pubid,
'name' = sp.name,
'restricted' = 0,
'status' = sp.status,
-- using 'task' is for backward compatibilty
'task' = convert(int, 1),
'replication frequency' = sp.repl_freq,
'synchronization method' = sp.sync_method,
'description' = sp.description,
'immediate_sync' = sp.immediate_sync,
'enabled_for_internet' = sp.enabled_for_internet,
'allow_push' = sp.allow_push,
'allow_pull' = sp.allow_pull,
'allow_anonymous' = sp.allow_anonymous,
'independent_agent' = sp.independent_agent,
'immediate_sync_ready' = sp.immediate_sync_ready,
-- SyncTran
'allow_sync_tran' = sp.allow_sync_tran,
'autogen_sync_procs' = sp.autogen_sync_procs,
'snapshot_jobid' = sp.snapshot_jobid,
'retention' = sp.retention,
'has subscription' = CASE WHEN EXISTS
(
SELECT *
FROM IHsubscriptions
WHERE article_id IN
(
SELECT article_id
FROM IHarticles
WHERE publication_id = sp.pubid
)
)
THEN 1 ELSE 0 END,
'allow_queued_tran' = sp.allow_queued_tran,
-- Portable snapshot
'snapshot_in_defaultfolder' = sp.snapshot_in_defaultfolder,
'alt_snapshot_folder' = sp.alt_snapshot_folder,
-- Pre/post-snapshot commands
'pre_snapshot_script' = sp.pre_snapshot_script,
'post_snapshot_script' = sp.post_snapshot_script,
-- Snapshot compression
'compress_snapshot' = sp.compress_snapshot,
-- Post 7.0 ftp support
'ftp_address' = sp.ftp_address,
'ftp_port' = sp.ftp_port,
'ftp_subdirectory' = sp.ftp_subdirectory,
'ftp_login' = sp.ftp_login,
'allow_dts' = sp.allow_dts,
'allow_subscription_copy' = sp.allow_subscription_copy,
-- 7.5 Queued updates
'centralized_conflicts' = NULL,
'conflict_retention' = 14,
'conflict_policy' = NULL,
'queue_type' = NULL,
'backward_comp_level' = sp.backward_comp_level,
'publish_to_AD' = CASE
WHEN sp.ad_guidname IS NULL
THEN 0 ELSE 1 END,
'allow_initialize_from_backup' = sp.allow_initialize_from_backup,
'replicate_ddl' = sp.replicate_ddl,
-- PeerToPeer only (for now, not allowed in hetero)
'enabled_for_p2p' = 0,
'publish_local_changes_only' = 0,
-- Enabled for heterogeneous subscribers only
'enabled_for_het_sub' = case when (sp.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB then 1 else 0 end
FROM syspublications sp,
MSpublications msp,
master.dbo.sysservers ss,
#accessiblepubs acc
WHERE sp.pubid = msp.publication_id
AND msp.publisher_id = ss.srvid
AND UPPER(ss.srvname collate database_default) = UPPER(@publisher) collate database_default
AND ((sp.name = @publication) or (@publication = N'%'))
AND sp.pubid = acc.pubid
ORDER BY sp.name
RETURN (0)
END