Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHsyncmetadata

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_IHsyncmetadata
(
    @publisher          sysname,
    @source_owner       sysname,
    @source_table       sysname,
    @refresh            bit = 0
)
AS
BEGIN
    DECLARE @retcode            int
    DECLARE @publisher_type     sysname
    DECLARE @publisher_id       int
    DECLARE @name               nvarchar(255)
    DECLARE @owner              nvarchar(255)
    DECLARE @uniqueness         nvarchar(255)
    DECLARE @column_name        sysname
    DECLARE @table_name         sysname
    DECLARE @table_id           int
    DECLARE @column_pos         int
    DECLARE @column_id          int
    DECLARE @constraint_id      int
    DECLARE @index_id           int
    DECLARE @publisher_dbms		sysname
    DECLARE @publisher_version	sysname

    SET NOCOUNT ON

    -- Get publisher info
    EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher      = @publisher,
                                                    @publisher_type = @publisher_type OUTPUT,
                                                    @publisher_id   = @publisher_id   OUTPUT

    -- Reject unsupported publisher types
    IF @publisher_type NOT IN (N'ORACLE', N'ORACLE GATEWAY')
    BEGIN
        RAISERROR (21645, 16, -1, @publisher_type)
        RETURN (1)
    END
    ELSE
    BEGIN
		-- Set publisher DBMS and version
		SELECT	@publisher_dbms		= 'ORACLE'

		EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
											@version	= @publisher_version OUTPUT
		
		IF @retcode != 0 OR @@ERROR != 0
		BEGIN
			RETURN (1)
		END
	END

    -- If refresh requested, drop existing metadata so it will be repopulated
    /*
    IF @refresh = 1
    BEGIN

    END
    */

    -- Check metadata cache to see if already cached
    -- Metadata will not be found if cleared by refresh
    -- or if it has not previously been published
    SELECT  @table_id = table_id
    FROM    dbo.IHpublishertables
    WHERE   publisher_id = @publisher_id
      AND   name  = @source_table
      AND   owner = @source_owner

    IF @table_id IS NOT NULL
    BEGIN
        -- Metadata is already available
        RETURN (0)
    END

    -- Create temp tables for publisher metadata
    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)
    )

    if (@publisher_type LIKE N'ORACLE%')
    BEGIN
        -- Populate the metadata tables
        EXEC @retcode = sys.sp_ORAgetcolumndata @owner      = @source_owner,
                                                @tablename  = @source_table,
                                                @publisher  = @publisher

        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (21781, 16, -1)
            RETURN (1)
        END
    END

    
    -- Populate IHpublishertables
    
    INSERT INTO IHpublishertables
    (
        publisher_id,
        name,
        owner
    )
    SELECT  @publisher_id,
            @source_table,
            @source_owner

    IF @@ERROR <> 0
        return (1)

    SELECT @table_id = @@IDENTITY

    
    -- Populate IHpublishercolumns
    
    INSERT INTO IHpublishercolumns
    (
        table_id,
        publisher_id,
        name,
        column_ordinal,
        type,
        length,
        prec,
        scale,
        isnullable,
        iscaptured
    )
    SELECT  @table_id,
            @publisher_id,
            name,
            column_ordinal,
            type,
            length,
            prec,
            scale,
            isnullable,
            iscaptured
    FROM    #publishercolumns
    WHERE	sys.fn_MSrepl_checktype(type, @publisher_dbms, @publisher_version) = 1
    ORDER BY column_ordinal

    IF @@ERROR <> 0
        RETURN (1)

    DROP TABLE #publishercolumns

    
    --  Populate IHpublisherConstraints
    --  Currently only getting the Primary Key constraint
    
    DECLARE constraint_cur CURSOR FAST_FORWARD FOR
    SELECT  constraint_name,
            table_owner,
            table_name,
            column_name,
            column_position
    FROM    #constraints

    OPEN constraint_cur

    FETCH NEXT FROM constraint_cur
    INTO @name, @owner, @table_name, @column_name, @column_pos

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- Clear column_id and constraint_id
        SET @column_id     = NULL
        SET @constraint_id = NULL

        --  Get Column ID
        SELECT  @column_id = publishercolumn_id
        FROM    IHpublishercolumns
        WHERE   publisher_id = @publisher_id
          AND   table_id     = @table_id
          AND   name         = @column_name
        ORDER BY column_ordinal

        IF @@ERROR <> 0
        BEGIN
            CLOSE constraint_cur
            DEALLOCATE constraint_cur
            DROP TABLE #constraints
            RETURN (1)
        END

        IF @column_id IS NULL
        BEGIN
            RAISERROR(21613, 16, -1, @column_name, @source_table)
            CLOSE constraint_cur
            DEALLOCATE constraint_cur
            DROP TABLE #constraints
            RETURN (1)
        END

        -- Get constraint ID, if it has already been added
        SELECT @constraint_id   = publisherconstraint_id
        FROM   IHpublisherconstraints
        WHERE  name = @name
        AND    table_id = @table_id
        AND    publisher_id = @publisher_id

        IF @@ERROR <> 0
        BEGIN
            CLOSE constraint_cur
            DEALLOCATE constraint_cur
            DROP TABLE #constraints
            RETURN (1)
        END

        -- if constraint does not exist add it
        IF @constraint_id IS NULL
        BEGIN
            INSERT INTO IHpublisherconstraints (name, table_id, publisher_id, type)
            VALUES (@name, @table_id, @publisher_id, 'PRIMARYKEY')

            IF @@ERROR <> 0
            BEGIN
                CLOSE constraint_cur
                DEALLOCATE constraint_cur
                DROP TABLE #constraints
                RETURN (1)
            END

            SELECT @constraint_id = @@IDENTITY
        END

        -- associate the table columns to the constraint
        INSERT INTO IHpublishercolumnconstraints (publishercolumn_id, publisherconstraint_id, indid)
        VALUES (@column_id, @constraint_id, @column_pos)

        IF @@ERROR <> 0
        BEGIN
            CLOSE constraint_cur
            DEALLOCATE constraint_cur
            DROP TABLE #constraints
            RETURN (1)
        END

        FETCH NEXT FROM constraint_cur
        INTO @name, @owner, @table_name, @column_name, @column_pos
    END

    CLOSE constraint_cur
    DEALLOCATE constraint_cur
    DROP TABLE #constraints

    
    -- Populate IHpublisherindexes
    
    DECLARE index_cur CURSOR FAST_FORWARD FOR
    SELECT  index_name,
            uniqueness,
            column_name,
            column_position
    FROM    #indexes

    OPEN index_cur

    FETCH NEXT FROM index_cur
    INTO @name, @uniqueness, @column_name, @column_pos

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- Clear column and index id's
        SET @index_id  = NULL
        SET @column_id = NULL

        SELECT  @column_id = publishercolumn_id
        FROM    IHpublishercolumns
        WHERE   publisher_id = @publisher_id
          AND   table_id     = @table_id
          AND   name         = @column_name
        ORDER BY column_ordinal

        IF @@ERROR <> 0
        BEGIN
            CLOSE index_cur
            DEALLOCATE index_cur
            DROP TABLE #indexes
            RETURN (1)
        END

        IF @column_id IS NULL
        BEGIN
            RAISERROR(21614, 16, -1, @column_name, @source_table)
            CLOSE index_cur
            DEALLOCATE index_cur
            DROP TABLE #indexes
            RETURN (1)
        END

        -- Find the index and add entry for this key column,
        -- if it doens't already exist
        SELECT  @index_id = publisherindex_id
        FROM    IHpublisherindexes
        WHERE   publisher_id = @publisher_id
          AND   table_id     = @table_id
          AND   name         = @name

        IF @@ERROR <> 0
        BEGIN
            CLOSE index_cur
            DEALLOCATE index_cur
            DROP TABLE #indexes
            RETURN (1)
        END

        IF @index_id IS NULL
        BEGIN
            INSERT INTO IHpublisherindexes (table_id, publisher_id, name, type)
            VALUES (@table_id, @publisher_id, @name, @uniqueness)

            IF @@ERROR <> 0
            BEGIN
                CLOSE index_cur
                DEALLOCATE index_cur
                DROP TABLE #indexes
                RETURN (1)
            END

            SELECT @index_id = @@IDENTITY
        END

        -- associate the index to the column
        INSERT INTO IHpublishercolumnindexes (publishercolumn_id, publisherindex_id, indid)
        VALUES (@column_id, @index_id, @column_pos)

        IF @@ERROR <> 0
        BEGIN
            CLOSE index_cur
            DEALLOCATE index_cur
            DROP TABLE #indexes
            RETURN (1)
        END

        FETCH NEXT FROM index_cur
        INTO @name, @uniqueness, @column_name, @column_pos
    END

    CLOSE index_cur
    DEALLOCATE index_cur
    DROP TABLE #indexes

    RETURN 0
END

 
Last revision SQL2008SP2
See also

  sp_MSdrop_repltran (Procedure)
sp_ORAgetcolumndata (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