-- 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