-- Name: sp_ORAhelparticle
-- Description:
-- Oracle specific help article
-- NOTE:
-- RMO flag tells helparticle to report extended
-- HREPL columns (which will be NULL for SQL publishers)
create procedure sys.sp_ORAhelparticle
(
@pubid int,
@article sysname,
@returnfilter bit,
@publisher sysname,
@found int OUTPUT,
@rmo bit = 0
)
AS
BEGIN
DECLARE @retcode int
SET NOCOUNT ON
SELECT @found = 0
-- Use explicit converts for constants/computed columns because the UI is
-- very sensitive to data type. Use BOL as a reference
IF (@rmo = 0)
BEGIN
-- Normal case
SELECT 'article id' = iha.article_id,
'article name' = iha.name,
'base object' = CONVERT(nvarchar(257), QUOTENAME(msa.source_owner) + '.' + QUOTENAME(msa.source_object)),
'destination object' = iha.dest_table,
'synchronization object' = CONVERT(nvarchar(257), NULL),
-- Shiloh defined sp_helparticle's type to be a smallint (via the temp table)
-- To maintain UI backcompat, convert to smallint explicitly.
'type' = CONVERT(smallint, iha.type),
'status' = CONVERT(int, iha.status & ~32),
'filter' = CONVERT(nvarchar(257), NULL),
'description' = msa.description,
'insert_command' = iha.ins_cmd,
'update_command' = iha.upd_cmd,
'delete_command' = iha.del_cmd,
'creation script path' = iha.creation_script,
'vertical partition' = CASE WHEN
(
SELECT count(*)
FROM IHpublishercolumns ihpc
WHERE ihpc.table_id = iha.table_id
) !=
(
select count(*)
FROM IHcolumns ihc
WHERE ihc.article_id = iha.article_id
) THEN CONVERT(bit, 1)
ELSE CONVERT(bit, 0)
END,
'pre_creation_cmd' = iha.pre_creation_cmd,
'filter_clause' = CASE
WHEN @returnfilter = 1 THEN iha.filter_clause
ELSE CONVERT(ntext, NULL)
END,
'schema_option' = iha.schema_option,
'dest_owner' = iha.dest_owner,
'source_owner' = msa.source_owner,
'unqua_source_object' = msa.source_object,
'sync_object_owner' = iha.article_view_owner,
'unqualified_sync_object' = iha.article_view,
'filter_owner' = CONVERT(sysname, NULL),
'unqua_filter' = CONVERT(sysname, NULL),
'auto_identity_range' = CONVERT(bit, 0),
'publisher_identity_range' = CONVERT(bigint, NULL),
'identity_range' = CONVERT(bigint, NULL),
'threshold' = CONVERT(bigint, NULL),
'identityrangemanagementoption' = CONVERT(int, 0),
'fire_triggers_on_snapshot' = iha.fire_triggers_on_snapshot
FROM IHpublications ihp,
IHarticles iha,
MSarticles msa
WHERE ihp.pubid = iha.publication_id
AND ihp.pubid = msa.publication_id
AND iha.article_id = msa.article_id
AND ihp.pubid = @pubid
AND ((@article = N'%') or (iha.name = @article))
ORDER BY iha.name
END
ELSE
BEGIN
-- RMO version
CREATE TABLE #ORAtablespaceinfo
(
article_id int,
tablespace sysname
)
-- Get tablespace information
EXEC @retcode = sp_ORAGetTablespaceInfo @publisher = @publisher
IF @retcode != 0 OR @@ERROR != 0
BEGIN
DROP TABLE #ORAtablespaceinfo
RETURN (1)
END
SELECT 'article id' = iha.article_id,
'article name' = iha.name,
'base object' = CONVERT(nvarchar(257), QUOTENAME(msa.source_owner) + '.' + QUOTENAME(msa.source_object)),
'destination object' = iha.dest_table,
'synchronization object' = CONVERT(nvarchar(257), NULL),
-- Shiloh defined sp_helparticle's type to be a smallint (via the temp table)
-- To maintain UI backcompat, convert to smallint explicitly.
'type' = CONVERT(smallint, iha.type),
'status' = CONVERT(int, iha.status & ~32),
'filter' = CONVERT(nvarchar(257), NULL),
'description' = msa.description,
'insert_command' = iha.ins_cmd,
'update_command' = iha.upd_cmd,
'delete_command' = iha.del_cmd,
'creation script path' = iha.creation_script,
'vertical partition' = CASE WHEN
(
SELECT count(*)
FROM IHpublishercolumns ihpc
WHERE ihpc.table_id = iha.table_id
) !=
(
select count(*)
FROM IHcolumns ihc
WHERE ihc.article_id = iha.article_id
) THEN CONVERT(bit, 1)
ELSE CONVERT(bit, 0)
END,
'pre_creation_cmd' = iha.pre_creation_cmd,
'filter_clause' = CASE
WHEN @returnfilter = 1 THEN iha.filter_clause
ELSE CONVERT(ntext, NULL)
END,
'schema_option' = iha.schema_option,
'dest_owner' = iha.dest_owner,
'source_owner' = msa.source_owner,
'unqua_source_object' = msa.source_object,
'sync_object_owner' = iha.article_view_owner,
'unqualified_sync_object' = iha.article_view,
'filter_owner' = CONVERT(sysname, NULL),
'unqua_filter' = CONVERT(sysname, NULL),
'auto_identity_range' = CONVERT(bit, 0),
'publisher_identity_range' = CONVERT(bigint, NULL),
'identity_range' = CONVERT(bigint, NULL),
'threshold' = CONVERT(bigint, NULL),
'identityrangemanagementoption' = CONVERT(int, 0),
'fire_triggers_on_snapshot' = iha.fire_triggers_on_snapshot,
'tablespace' = otsi.tablespace,
'use_default_datatypes' = iha.use_default_datatypes
FROM IHpublications ihp INNER JOIN IHarticles iha ON ihp.pubid = iha.publication_id
INNER JOIN MSarticles msa ON ihp.pubid = msa.publication_id
LEFT OUTER JOIN #ORAtablespaceinfo otsi ON iha.article_id = otsi.article_id
WHERE iha.article_id = msa.article_id
AND ihp.pubid = @pubid
AND ((@article = N'%') or (iha.name = @article))
ORDER BY iha.name
DROP TABLE #ORAtablespaceinfo
END
IF @@rowcount > 0
BEGIN
SELECT @found = 1
END
RETURN (0)
END