Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_get_Oracle_publisher_metadata

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_get_Oracle_publisher_metadata

-- Description:
--          Specialized stored procedure for repldiag tool to gather
--          Oracle publishing meta data from Oracle publisher.

-- Arguments:
--          @publisher -- Name of the Oracle publisher to be interogated.
--          @database -- Name of database where meta data for publishers is to be deposited.

-- Security:
--          Internal

-- Returns:
--          Result set:  Subscription status

-- Owner:
--          sward

create procedure sys.sp_MSrepl_get_Oracle_publisher_metadata
(
	@publisher	sysname,
	@database sysname
)
AS
BEGIN
    SET NOCOUNT ON

    -- Declarations.
    DECLARE @retcode			int
    DECLARE @InsColumnList		nvarchar(2000)
    DECLARE @SelectColumnList	nvarchar(2000)
    DECLARE @query				nvarchar(2000)
    DECLARE @LocalTableName		nvarchar(2000)
    DECLARE @cmd				nvarchar(2000)
    DECLARE @select				nvarchar(2000)

    DECLARE @sev			int
    DECLARE @state			int
    DECLARE @msg			nvarchar(max)
    DECLARE @version		nvarchar(64)
    DECLARE @product		nvarchar(64)
    DECLARE @publisher_version	sysname
    DECLARE @publisher_suffix   sysname

    SET NOCOUNT ON
	
    -- Get publisher version
    EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
					@version	= @publisher_version OUTPUT

    IF @retcode != 0 OR @@ERROR != 0
    BEGIN
		RETURN (1)
    END

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

    -- Define temp table
  	CREATE TABLE #version
    (
		version		nvarchar(64),
		product		nvarchar(64)
    )
	
    -- populate a temp table with a list of table columns from the Oracle publisher
    SELECT @InsColumnList    = 'version, product'
    SELECT @SelectColumnList = 'VERSION, PRODUCT'

    INSERT INTO #hquery(cmd) VALUES('SELECT VERSION, PRODUCT FROM PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE ''%ORACLE%''')
	
    EXEC @retcode = sys.sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#version',
					@InsColumnList		= @InsColumnList

    IF @@ERROR = 0 AND @retcode = 0
    BEGIN
		SELECT @version = version, @product = product FROM #version
   		SELECT @cmd = N'For Oracle publisher ' + @publisher + N', product is ' + @product + N'; version is ' + @version  		
   		EXEC sys.sp_log_repository_message N'SERVERPROPERTY', @cmd, @database
    END

    DROP TABLE #version

    -- Create temp tables for publisher metadata
    CREATE TABLE #HREPL_EVENT
    (
		EVENT_POLLID  		INT,
		EVENT_PUBLICATION_ID 	INT,
		EVENT_ARTICLE_ID 	INT,
		EVENT_TABLE_ID  	INT,
		EVENT_OPERATION  	INT,
		EVENT_SEQ  		INT,
		EVENT_ROWCNT  		INT,
		EVENT_CMDTYPE  		INT,
		EVENT_ENTRYTIME  	INT,
		EVENT_DIRECTORY  	VARCHAR(512),
		EVENT_SCRIPTSCH  	VARCHAR(512),
		EVENT_SCRIPTIDX  	VARCHAR(512),
		EVENT_COMMAND  		VARCHAR(1000),
		EVENT_PRESCRIPT  	VARCHAR(512),
		EVENT_POSTSCRIPT  	VARCHAR(512),
		EVENT_CREATIONSCRIPTPATH VARCHAR(256),
		EVENT_FTPADDRESS  	VARCHAR(512),
		EVENT_FTPPORT   	VARCHAR(42),
		EVENT_FTPSUBDIRECTORY  	VARCHAR(1020),
		EVENT_FTPLOGIN   	VARCHAR(512),
		EVENT_FTPPASSWORD  	VARCHAR(2096),
		EVENT_ALTERNATESNAPSHOTFOLDER VARCHAR(256),
		EVENT_COMPRESSSNAPSHOT  VARCHAR(256)
    )

    CREATE TABLE #HREPL_POLL
    (
		POLL_ROWID		VARCHAR(30) NOT NULL,
		POLL_POLLID		INT NULL,
		POLL_UPDATEFLAG		INT,
		POLL_ROWCNT		INT,
		POLL_TABLEID		INT
    )

    CREATE TABLE #HREPL_PUBLISHEDTABLES
    (
		PUBLISHED_OWNER   		VARCHAR(256) NOT NULL,
		PUBLISHED_TABLE   		VARCHAR(256) NOT NULL,
		PUBLISHED_TABLEID 		INT NOT NULL,
		PUBLISHED_ARTICLEID 		INT NOT NULL,
		PUBLISHED_LOGINSTANCE 		INT NOT NULL,
		PUBLISHED_ARTICLEINSTANCE 	INT NOT NULL,
		PUBLISHED_LOGDROPPENDING 	INT NOT NULL,
		PUBLISHED_ARTICLEDROPPENDING 	INT NOT NULL
    )

    CREATE TABLE #HREPL_PUBLISHER
    (
		Publisher_GUID        	VARCHAR(38) NOT NULL,
		Publisher_Name  	VARCHAR(128) NULL,
		Publisher_ID		INT NOT NULL,
		Publisher_PollInProcess	INT,
		Publisher_CurrentPollid	INT,
		Publisher_LSN		BINARY(10),
		Publisher_HasChanges	INT,
		Publisher_SyncInits		INT,
		Publisher_SyncDones		INT,
		Publisher_RowCntValidations	INT,
		Publisher_TableChanges		INT,
		Publisher_InReconciliation	INT,
		Publisher_TracerTokens		INT,
		Publisher_XactSetEnabled	INT,
		Publisher_XactSetBeginTime	INT,
		Publisher_XactSetInterval	INT,
		Publisher_Timestamp		VARCHAR(128)
    )

    CREATE TABLE #HREPL_DISTRIBUTOR
    (
		DISTRIBUTOR_PUBLISHERNAME 	VARCHAR(128) NOT NULL,
		DISTRIBUTOR_DISTRIBUTORNAME VARCHAR(128) NOT NULL,
		DISTRIBUTOR_DISTRIBUTIONDB 	VARCHAR(128) NOT NULL,
		DISTRIBUTOR_PRODUCTVERSION 	VARCHAR(128) NULL,
		DISTRIBUTOR_STATUS  		DECIMAL(4)   NOT NULL
    )

    CREATE TABLE #HREPL_XACTSETJOB
    (
		XACTSETJOB_ENABLED 		VARCHAR(1),
		XACTSETJOB_INTERVAL 		INT,
		XACTSETJOB_THRESHOLD 		INT,
		XACTSETJOB_LRINTERVAL 		INT,
		XACTSETJOB_LRTHRESHOLD 	INT,
		XACTSETJOB_WHAT  		VARCHAR(400),
		XACTSETJOB_LRISACTIVE 		VARCHAR(1),
		XACTSETJOB_INACTIVITYCOUNT 	INT
    )

    CREATE TABLE #HREPL_SCHEMAFILTER
    (
		SCHEMAFILTER_NAME	VARCHAR(200)
    )

    CREATE TABLE #HREPL_MUTEX
    (
		MUTEX_LOCK	INT
    )

    CREATE TABLE #HREPL_CHANGES
    (
		CHANGECOUNT	INT
    )

    CREATE TABLE #HREPL_VERSION
    (
		SQLSERVERVERSION	VARCHAR(256)
    )

    CREATE TABLE #HREPL_ARTICLELOG
    (
		HREPL_SEQ INT NULL,
		HREPL_STMT INT NULL,
		HREPL_OPERATION INT NOT NULL,
		HREPL_POLLID INT NULL,
		HREPL_ENTRYTIME INT NULL
    )

    CREATE TABLE #HREPL_LOGTABLES
    (
		TABLE_NAME varchar(30)
    )

    CREATE TABLE #ALL_VIEWS
    (
		VIEW_NAME varchar(30),
		TEXT      varchar(max)		
    )

    CREATE TABLE #ALL_TRIGGERS
    (
		TRIGGER_NAME varchar(30),
		TRIGGER_TYPE varchar(16),
		TRIGGERING_EVENT varchar(227),
		TABLE_OWNER  varchar(30),
		BASE_OBJECT_TYPE varchar(16),
		TABLE_NAME varchar(30),
		STATUS varchar(8),
		ACTION_TYPE varchar(11),
		TRIGGER_BODY varchar(max)
    )

    CREATE TABLE #ALL_INDEXES
    (
		INDEX_NAME varchar(30),
		INDEX_TYPE varchar(27),
		TABLE_NAME varchar(30),
		UNIQUENESS varchar(9),
		TABLESPACE_NAME varchar(30),
		INITIAL_EXTENT  int,
		NEXT_EXTENT int,
		MIN_EXTENTS int,
		MAX_EXTENTS int,
		PCT_INCREASE real,
		PCT_THRESHOLD real,
		INCLUDE_COLUMN real,
		PCT_FREE real,
		LOGGING varchar(3),
		DISTINCT_KEYS int,
		STATUS varchar(8),
		NUM_ROWS int,
		SAMPLE_SIZE int,
		LAST_ANALYZED datetime,
		DEGREE varchar(40),
		USER_STATS varchar(3),
		PCT_DIRECT_ACCESS real,
		JOIN_INDEX varchar(3)
	)
	
    CREATE TABLE #ALL_CONSTRAINTS
    (
        OWNER varchar(30),
        CONSTRAINT_NAME varchar(30),
        CONSTRAINT_TYPE varchar(1),
        TABLE_NAME varchar(30),
        SEARCH_CONDITION varchar(max),
        R_OWNER varchar(30),
        R_CONSTRAINT_NAME varchar(30),
        DELETE_RULE varchar(9),
        STATUS varchar(8),
        DEFERRABLE varchar(14),
        DEFERRED varchar(9),
        VALIDATED varchar(13),
        GENERATED varchar(14),
        BAD varchar(3),
        RELY varchar(4),
        LAST_CHANGE datetime,
        INDEX_OWNER varchar(30),
        INDEX_NAME varchar(30),
        INVALID varchar(7),
        VIEW_RELATED varchar(14)
    )

    CREATE TABLE #ALL_OBJECTS
    (
        OWNER varchar(30),
        OBJECT_NAME varchar(30),
        OBJECT_TYPE varchar(18),
        CREATED datetime,
        LAST_DDL_TIME datetime,
        TIMESTAMP varchar(19),
        STATUS varchar(7),
        TEMPORARY varchar(1),
        GENERATED varchar(1),
        SECONDARY varchar(1)
    )

    -- If the publisher name is less than or equal to 95 characters, then the publisher name alone
    -- when used as a table name suffix will guarantee that the table names are unique. If, however,
    -- the publisher name exceeds 95 characters, we insure that the resulting suffix is unique by
    -- appending to it characters drawn from a generated uniqueidentifier.
    IF LEN(@publisher) > 95
		SELECT @publisher_suffix = substring(@publisher,1,95) + substring(CONVERT(nvarchar(36), NEWID()),1,5)
	ELSE
		SELECT @publisher_suffix = @publisher
		
    -- Populate HREPL_EVENT
    SELECT @InsColumnList    = 'EVENT_POLLID,EVENT_PUBLICATION_ID,EVENT_ARTICLE_ID,EVENT_TABLE_ID,EVENT_OPERATION,EVENT_SEQ,EVENT_ROWCNT,EVENT_CMDTYPE,EVENT_ENTRYTIME,EVENT_DIRECTORY,EVENT_SCRIPTSCH,EVENT_SCRIPTIDX,EVENT_COMMAND,EVENT_PRESCRIPT,EVENT_POS
TSCRIPT,EVENT_CREATIONSCRIPTPATH,EVENT_FTPADDRESS,EVENT_FTPPORT,EVENT_FTPSUBDIRECTORY,EVENT_FTPLOGIN,EVENT_ALTERNATESNAPSHOTFOLDER,EVENT_COMPRESSSNAPSHOT'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT EVENT_POLLID,EVENT_PUBLICATION_ID,EVENT_ARTICLE_ID,EVENT_TABLE_ID,EVENT_OPERATION,EVENT_SEQ,EVENT_ROWCNT,EVENT_CMDTYPE,EVENT_ENTRYTIME,EVENT_DIRECTORY,EVENT_SCRIPTSCH,EVENT_SCRIPTID
X,EVENT_COMMAND,EVENT_PRESCRIPT,EVENT_POSTSCRIPT,EVENT_CREATIONSCRIPTPATH,EVENT_FTPADDRESS,EVENT_FTPPORT,EVENT_FTPSUBDIRECTORY,EVENT_FTPLOGIN,EVENT_ALTERNATESNAPSHOTFOLDER,EVENT_COMPRESSSNAPSHOT FROM HREPL_EVENT'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_EVENT_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_EVENT',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database

    -- Populate HREPL_POLL
    SELECT @InsColumnList    = 'POLL_ROWID,POLL_POLLID,POLL_UPDATEFLAG,POLL_ROWCNT,POLL_TABLEID'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT POLL_ROWID,POLL_POLLID,POLL_UPDATEFLAG,POLL_ROWCNT,POLL_TABLEID FROM HREPL_POLL'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_POLL_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_POLL',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_PUBLISHEDTABLES
    SELECT @InsColumnList    = 'PUBLISHED_OWNER,PUBLISHED_TABLE,PUBLISHED_TABLEID,PUBLISHED_ARTICLEID,PUBLISHED_LOGINSTANCE,PUBLISHED_ARTICLEINSTANCE,PUBLISHED_LOGDROPPENDING,PUBLISHED_ARTICLEDROPPENDING'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT PUBLISHED_OWNER,PUBLISHED_TABLE,PUBLISHED_TABLEID,PUBLISHED_ARTICLEID,PUBLISHED_LOGINSTANCE,PUBLISHED_ARTICLEINSTANCE,PUBLISHED_LOGDROPPENDING,PUBLISHED_ARTICLEDROPPENDING FROM HREP
L_PUBLISHEDTABLES'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_PUBLISHEDTABLES_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_PUBLISHEDTABLES',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_PUBLISHER
    SELECT @InsColumnList    = 'Publisher_GUID,Publisher_Name,Publisher_ID,Publisher_PollInProcess,Publisher_CurrentPollid,Publisher_LSN,Publisher_HasChanges,Publisher_SyncInits,Publisher_SyncDones,Publisher_RowCntValidations,Publisher_TableChanges,Publis
her_InReconciliation,Publisher_TracerTokens,Publisher_XactSetEnabled,Publisher_XactSetBeginTime,Publisher_XactSetInterval,Publisher_Timestamp'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT Publisher_GUID,Publisher_Name,Publisher_ID,Publisher_PollInProcess,Publisher_CurrentPollid,Publisher_LSN,Publisher_HasChanges,Publisher_SyncInits,Publisher_SyncDones,Publisher_RowCn
tValidations,Publisher_TableChanges,Publisher_InReconciliation,Publisher_TracerTokens,Publisher_XactSetEnabled,Publisher_XactSetBeginTime,Publisher_XactSetInterval,Publisher_Timestamp FROM HREPL_PUBLISHER'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_PUBLISHER_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_PUBLISHER',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database

    -- Populate #HREPL_DISTRIBUTOR
    SELECT @InsColumnList    = 'DISTRIBUTOR_PUBLISHERNAME,DISTRIBUTOR_DISTRIBUTORNAME,DISTRIBUTOR_DISTRIBUTIONDB,DISTRIBUTOR_PRODUCTVERSION,DISTRIBUTOR_STATUS'		
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT DISTRIBUTOR_PUBLISHERNAME,DISTRIBUTOR_DISTRIBUTORNAME,DISTRIBUTOR_DISTRIBUTIONDB,DISTRIBUTOR_PRODUCTVERSION,DISTRIBUTOR_STATUS FROM HREPL_DISTRIBUTOR'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_DISTRIBUTOR_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_DISTRIBUTOR',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_XACTSETJOB
    SELECT @InsColumnList    = 'XACTSETJOB_ENABLED,XACTSETJOB_INTERVAL,XACTSETJOB_THRESHOLD,XACTSETJOB_LRINTERVAL,XACTSETJOB_LRTHRESHOLD,XACTSETJOB_WHAT,XACTSETJOB_LRISACTIVE,XACTSETJOB_INACTIVITYCOUNT'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT XACTSETJOB_ENABLED,XACTSETJOB_INTERVAL,XACTSETJOB_THRESHOLD,XACTSETJOB_LRINTERVAL,XACTSETJOB_LRTHRESHOLD,XACTSETJOB_WHAT,XACTSETJOB_LRISACTIVE,XACTSETJOB_INACTIVITYCOUNT FROM HREPL_
XACTSETJOB'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_XACTSETJOB_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_XACTSETJOB',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_SCHEMAFILTER
    SELECT @InsColumnList    = 'SCHEMAFILTER_NAME'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT SCHEMAFILTER_NAME FROM HREPL_SCHEMAFILTER'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_SCHEMAFILTER_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_SCHEMAFILTER',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_MUTEX
    SELECT @InsColumnList    = 'MUTEX_LOCK'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT * FROM HREPL_MUTEX'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_MUTEX_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_MUTEX',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_CHANGES
    SELECT @InsColumnList    = 'CHANGECOUNT'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT CHANGECOUNT FROM HREPL_CHANGES'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_CHANGES_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_CHANGES',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #HREPL_VERSION
    SELECT @InsColumnList    = 'SQLSERVERVERSION'
    SELECT @SelectColumnList = '*'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT SQLSERVERVERSION FROM HREPL_VERSION'')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_VERSION_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_VERSION',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #ALL_VIEWS
    SELECT @InsColumnList    = 'VIEW_NAME, TEXT'
    SELECT @SelectColumnList = 'VIEW_NAME, TEXT'
    SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER = USER AND VIEW_NAME LIKE ''''HREPL_%'''''')'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_ALL_VIEWS_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#ALL_VIEWS',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #ALL_TRIGGERS
    IF @publisher_version LIKE N'8.%'
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, NULL AS BASE_OBJECT_TYPE, TABLE_NAME, STATUS, NULL AS ACTION_TYPE, TRIGGER_BODY FROM ALL_TRIGGERS WHERE OWNER = USER AND
TRIGGER_NAME LIKE ''''HREPL_%'''''')'
    END
    ELSE
    BEGIN
     	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, STATUS, ACTION_TYPE, TRIGGER_BODY FROM ALL_TRIGGERS WHERE OWNER = USER AND TRIGGER_NAME LI
KE ''''HREPL_%'''''')'
    END
    SELECT @InsColumnList    = 'TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, STATUS, ACTION_TYPE, TRIGGER_BODY'
    SELECT @SelectColumnList = 'TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, STATUS, ACTION_TYPE, TRIGGER_BODY'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_ALL_TRIGGERS_' + @publisher_suffix)
	
    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#ALL_TRIGGERS',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #ALL_INDEXES
    IF @publisher_version LIKE N'8.%'
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, PCT_FREE, LOGGI
NG, DISTINCT_KEYS, STATUS, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, NULL AS USER_STATS, NULL AS PCT_DIRECT_ACCESS, NULL AS JOIN_INDEX FROM ALL_INDEXES WHERE OWNER = USER AND TABLE_NAME LIKE ''''HREPL_%'''''')'
    END
    ELSE
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, PCT_FREE, LOGGI
NG, DISTINCT_KEYS, STATUS, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, USER_STATS, PCT_DIRECT_ACCESS, JOIN_INDEX FROM ALL_INDEXES WHERE OWNER = USER AND TABLE_NAME LIKE ''''HREPL_%'''''')'
    END
    SELECT @InsColumnList    = 'INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, PCT_FREE, LOGGING, DISTINCT_KEYS, STATUS, NUM_ROWS, SAMPLE
_SIZE, LAST_ANALYZED, DEGREE, USER_STATS, PCT_DIRECT_ACCESS, JOIN_INDEX'
    SELECT @SelectColumnList = 'INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, PCT_FREE, LOGGING, DISTINCT_KEYS, STATUS, NUM_ROWS, SAMPLE
_SIZE, LAST_ANALYZED, DEGREE, USER_STATS, PCT_DIRECT_ACCESS, JOIN_INDEX'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_ALL_INDEXES_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#ALL_INDEXES',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #ALL_CONSTRAINTS
    IF @publisher_version LIKE N'8.%'
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, NULL AS DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, NUL
L AS RELY, LAST_CHANGE, NULL AS INDEX_OWNER, NULL AS INDEX_NAME, NULL AS INVALID, NULL AS VIEW_RELATED FROM ALL_CONSTRAINTS WHERE OWNER = USER AND TABLE_NAME LIKE ''''HREPL_%'''''')'
    END
    ELSE
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_
CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED FROM ALL_CONSTRAINTS WHERE OWNER = USER AND TABLE_NAME LIKE ''''HREPL_%'''''')'
    END
    SELECT @InsColumnList    = 'OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, V
IEW_RELATED'
    SELECT @SelectColumnList = 'OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, V
IEW_RELATED'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_ALL_CONSTRAINTS_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#ALL_CONSTRAINTS',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
    -- Populate #ALL_OBJECTS
    IF @publisher_version LIKE N'8.%'
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, NULL AS SECONDARY FROM ALL_OBJECTS WHERE OWNER = USER AND OBJECT_NAME LIKE ''''HRE
PL_%'''' UNION ALL SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, NULL AS SECONDARY FROM ALL_OBJECTS O, HREPL_PUBLISHEDTABLES T WHERE O.OWNER = T.PUBLISHED_OWNER AND O.OBJECT_NAME = T.PUBLISHED_TAB
LE'')'
    END
    ELSE
    BEGIN
    	SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY FROM ALL_OBJECTS WHERE OWNER = USER AND OBJECT_NAME LIKE ''''HREPL_%''''
 UNION ALL SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY FROM ALL_OBJECTS O, HREPL_PUBLISHEDTABLES T WHERE O.OWNER = T.PUBLISHED_OWNER AND O.OBJECT_NAME = T.PUBLISHED_TABLE'')'
    END
    SELECT @InsColumnList    = 'OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY'
    SELECT @SelectColumnList = 'OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY'
    SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename('HREPL_ALL_OBJECTS_' + @publisher_suffix)

    EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#ALL_OBJECTS',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
			
    -- Determine the names of the article log tables
    SELECT @InsColumnList    = 'TABLE_NAME'
    SELECT @SelectColumnList = '*'

    TRUNCATE TABLE #hquery
    INSERT INTO #hquery(cmd) VALUES ('SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER AND TABLE_NAME LIKE ''HREPL_ARTICLE%LOG%''')

    EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#HREPL_LOGTABLES',
					@InsColumnList		= @InsColumnList

    -- Populate the article log tables (meta data columns only)
    SELECT @InsColumnList    = 'HREPL_SEQ, HREPL_STMT, HREPL_OPERATION, HREPL_POLLID, HREPL_ENTRYTIME'
    SELECT @SelectColumnList = '*'

    DECLARE @TableName varchar(30)
    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR
        SELECT TABLE_NAME FROM #HREPL_LOGTABLES
    OPEN hC
    FETCH hC INTO @TableName
    WHILE (@@fetch_status <> -1)
    BEGIN
        -- Populate #HREPL_ARTICLELOG table
        TRUNCATE TABLE #HREPL_ARTICLELOG

        SELECT @query            = 'INSERT INTO #hquery(cmd) VALUES (''SELECT HREPL_SEQ, HREPL_STMT, HREPL_OPERATION, HREPL_POLLID, HREPL_ENTRYTIME FROM ' + sys.fn_replreplacesinglequote(@TableName) + ''')'
        SELECT @LocalTableName   = quotename(@database) +  N'.dbo.' + quotename(@TableName + N'_' + @publisher_suffix)

        EXEC @retcode = sp_MSrepl_get_Oracle_publisher_table
					@query			    = @query,
					@SelectColumnList	= @SelectColumnList,
					@temptablename		= '#HREPL_ARTICLELOG',
					@InsColumnList		= @InsColumnList,
					@LocalTableName     = @LocalTableName,
					@publisher          = @publisher,
					@database			= @database
					
        FETCH hC INTO @TableName
    END
    CLOSE hC
    DEALLOCATE hC

    
    --  Drop Temp tables
    
    DROP TABLE #ALL_OBJECTS
    DROP TABLE #ALL_CONSTRAINTS
    DROP TABLE #ALL_INDEXES
    DROP TABLE #ALL_VIEWS
    DROP TABLE #ALL_TRIGGERS
    DROP TABLE #HREPL_ARTICLELOG
    DROP TABLE #HREPL_VERSION
    DROP TABLE #HREPL_CHANGES
    DROP TABLE #HREPL_SCHEMAFILTER
    DROP TABLE #HREPL_XACTSETJOB
    DROP TABLE #HREPL_DISTRIBUTOR
    DROP TABLE #HREPL_PUBLISHER
    DROP TABLE #HREPL_PUBLISHEDTABLES
    DROP TABLE #HREPL_POLL
    DROP TABLE #HREPL_EVENT

	-- Gather the column information for the publisher's published articles
    CREATE TABLE #publishercolumns
    (
        name            sysname collate database_default,
        column_ordinal  int,
        type            sysname collate database_default,
        length          int,
        prec            int,
        scale           int,
        isnullable      bit,
        iscaptured      bit
    )

    CREATE TABLE #indexes
    (
        index_name      nvarchar(30),
        index_owner     nvarchar(30),
        uniqueness      nvarchar(11),
        column_name     nvarchar(30),
        column_position int,
        index_status    nvarchar(8)
    )

    CREATE TABLE #constraints
    (
        constraint_name nvarchar(30),
        constraint_type nchar(1),
        table_owner     nvarchar(30),
        table_name      nvarchar(30),
        index_owner     nvarchar(30),
        index_name      nvarchar(30),
        column_name     nvarchar(30),
        column_position int,
        validated       nvarchar(13),
        cons_status     nvarchar(8),
        idx_status      nvarchar(8)
    )

    DECLARE @source_owner sysname
    DECLARE @source_table sysname
    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR
        SELECT art.source_owner, art.source_object FROM MSarticles art, master.dbo.sysservers sys
        WHERE art.publisher_id = sys.srvid
          AND sys.srvname = @publisher
    OPEN hC
    FETCH hC INTO @source_owner, @source_table
    WHILE (@@fetch_status <> -1)
    BEGIN
        -- Populate the metadata tables
        EXEC @retcode = sys.sp_ORAgetcolumndata @owner          = @source_owner,
                                                @tablename      = @source_table,
                                                @publisher      = @publisher,
                                                @fordiagnosis   = 1
        IF @retcode != 0 OR @@error != 0
        BEGIN
            SELECT @cmd = N'Query of publisher ' + @publisher + N' to get column data for table ' +
                          @source_owner + N'.' + @source_table + N' failed. '
            EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
            SELECT @retcode = 0
        END

        FETCH hC INTO @source_owner, @source_table
    END
    CLOSE hC
    DEALLOCATE hC

    SELECT @cmd = N'DROP TABLE ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHconstraints_' + sys.fn_replreplacesinglequote(@publisher_suffix))
    BEGIN TRY
        EXEC @retcode = sys.sp_executesql @cmd
    END TRY
    BEGIN CATCH
        SELECT @retcode = 0
    END CATCH

    SELECT @select = N'SELECT * INTO ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHconstraints_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' FROM #constraints'
    EXEC @retcode = sys.sp_executesql @select
    IF @retcode != 0 OR @@error != 0
    BEGIN
		SELECT @cmd = N'SELECT from table #constraints to populate table ' + sys.fn_replreplacesinglequote(@database) +
		              N'.dbo.' + quotename('HREPL_IHconstraints_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' failed: Query = ' + @select
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
    END
    ELSE
    BEGIN
		EXEC sys.sp_log_repository_message N'Success', @select, @database
    END

    SELECT @cmd = N'DROP TABLE ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHindexes_' + sys.fn_replreplacesinglequote(@publisher_suffix))
    BEGIN TRY
        EXEC @retcode = sys.sp_executesql @cmd
    END TRY
    BEGIN CATCH
        SELECT @retcode = 0
    END CATCH

    SELECT @select = N'SELECT * INTO ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHindexes_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' FROM #indexes'
    EXEC @retcode = sys.sp_executesql @select
    IF @retcode != 0 OR @@error != 0
    BEGIN
		SELECT @cmd = N'SELECT from table #indexes to populate table ' + sys.fn_replreplacesinglequote(@database) +
		              N'.dbo.' + quotename('HREPL_IHindexes_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' failed: Query = ' + @select
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
    END
    ELSE
    BEGIN
		EXEC sys.sp_log_repository_message N'Success', @select, @database
    END

    SELECT @cmd = N'DROP TABLE ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHpublishercolumns_' + sys.fn_replreplacesinglequote(@publisher_suffix))

    BEGIN TRY
        EXEC @retcode = sys.sp_executesql @cmd
    END TRY
    BEGIN CATCH
        SELECT @retcode = 0
    END CATCH

    SELECT @select = N'SELECT * INTO ' + quotename(@database) +  N'.dbo.' + quotename('HREPL_IHpublishercolumns_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' FROM #publishercolumns'
    EXEC @retcode = sys.sp_executesql @select
    IF @retcode != 0 OR @@error != 0
    BEGIN
		SELECT @cmd = N'SELECT from table #publishercolumns to populate table ' + sys.fn_replreplacesinglequote(@database) +
		              N'.dbo.' + quotename('HREPL_IHpublishercolumns_' + sys.fn_replreplacesinglequote(@publisher_suffix)) + N' failed: Query = ' + @select
		EXEC sys.sp_log_repository_message N'Failure', @cmd, @database
    END
    ELSE
    BEGIN
		EXEC sys.sp_log_repository_message N'Success', @select, @database
    END
    
    --  Drop Temp tables
    
    DROP TABLE #constraints
    DROP TABLE #indexes
    DROP TABLE #publishercolumns

END

 
Last revision 2008RTM
See also

  sp_get_Oracle_publisher_metadata (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash