-- Name:
--		sp_ORAhelpXactBatching

-- Description:
--		Return parity of the Xact Batching Enabled flag

-- Inputs:
--		@publisher		== name of Oracle publisher
--		@enabled		== enabled

-- Returns:
--		Return code (0 for success, 1 for failure)

-- Security:
--		internal stored procedure
-- Requires Certificate signature for catalog access


CREATE PROCEDURE sys.sp_ORAhelpXactBatching
(
	@publisher		sysname,
	@enabled		bit = NULL OUTPUT
)
AS
BEGIN
	DECLARE @retcode	int
	DECLARE @pubid		int
	DECLARE @cmd			nvarchar(400)
	DECLARE	@InsColumnList		nvarchar(200)
	DECLARE	@SelectColumnList	nvarchar(200)

	
	-- Security Check: require sysadmin
	
	IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
	BEGIN
		RAISERROR(21089,16,-1)
		RETURN (1)
	END

	-- Get publisher ID
	SELECT @pubid = srvid
	FROM	master.dbo.sysservers
	WHERE	UPPER(srvname collate database_default) = UPPER(@publisher)
	
	IF @pubid IS NULL
	BEGIN
		RAISERROR(21600, 16, -1, @publisher)
		RETURN (1)
	END

	-- Define sp_ORAremotequery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	-- Process help
	CREATE TABLE #XactSetJob
	(
		xactbatchingenabled		varchar(1)
	)
	
	
	-- populate a temp table with the result of a query of HREPL_Publisher Xact batching eanbled flag
	
	SELECT @InsColumnList    = 'xactbatchingenabled'
	SELECT @SelectColumnList = 'PUBLISHER_XACTSETENABLED'

	INSERT INTO #hquery(cmd) VALUES (N'SELECT PUBLISHER_XACTSETENABLED FROM HREPL_PUBLISHER');
	
	EXEC @retcode = sp_ORAremotequery
					@Server			= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable		= '#XactSetJob',
					@InsColumnList		= @InsColumnList

	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21787, 16, -1, @publisher)
		RETURN (1)
	END

	-- If no output parameters have been specified, return the result set
	IF @enabled IS NULL
	BEGIN
		SELECT * FROM #XactSetJob
		RETURN(0)
	END

	-- If @enabled is passed as a parameter, return the flag as an output
	-- parameter.
	IF @enabled IS NOT NULL
		SET @enabled = (SELECT CONVERT(bit,xactbatchingenabled) from #XactSetJob)

	RETURN (0)
END