SQL Server sp_addmergearticle - Schema option error (Msg 21222))when add two different schema_option from the same query (using while loop)

-2

I have a query that add articles to publication in a context of replication. But an error occurs when I add xtype 'table' and 'func schema only' in the same query.

Basically the query first loops on missing articles from sysmergearticles and creates a temporary table.

USE  quatre_saisons

-- Select missing articles
DECLARE table_cursor CURSOR FOR 
    SELECT 
        name,
        CASE 
            WHEN xtype = 'U' THEN 'table' 
            WHEN xtype = 'V' THEN 'view schema only'
            WHEN xtype = 'FN' THEN 'func schema only'
            WHEN xtype = 'P' THEN 'proc schema only'
            WHEN xtype = 'TF' THEN 'func schema only'
            ELSE 'func schema only'
         END AS xtype 
     FROM 
         sysobjects WITH (nolock) 
     WHERE 
         xtype IN ('U', 'V', 'FN', 'P', 'TF') 
         AND category <> 2 
         AND name NOT IN (SELECT DISTINCT object_name(objid) 
                          FROM sysmergearticles 
                          UNION ALL  
                          SELECT DISTINCT name 
                          FROM sysmergeschemaarticles) 

DECLARE @publication AS sysname = 'ThisPublication'
DECLARE @table AS sysname;
DECLARE @xtype AS varchar(50);

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table, @xtype

After I defined binary value for schema_option following microsoft and schema_option values from table sysmergearticles:

'table': schema_option = 0x000000010C034FD1 
'func schema only': schema_option = 0x0000000008000001

-- Set schema_option
DECLARE @schemaoptions varbinary(8) = 
CASE WHEN @xtype = 'table' THEN 0x000000010C034FD1 
     ELSE 0x0000000008000001
END

Finally I add merge articles with sp_addmergearticles through a WHILE LOOP

-- Add missing articles to publication
WHILE @@FETCH_STATUS = 0  
BEGIN
    EXEC sp_addmergearticle 
            @publication = @publication, 
            @article = @table, 
            @source_owner = N'dbo', 
            @source_object = @table, 
            @type = @xtype, 
            @description = N'', 
            @creation_script = N'', 
            @pre_creation_cmd = N'none', 
            @schema_option = @schemaoptions, 
            @identityrangemanagementoption = N'none', 
            @destination_owner = N'dbo', 
            @force_reinit_subscription = 1, 
            @column_tracking = N'false', 
            @subset_filterclause = N'', 
            @vertical_partition = N'false', 
            @verify_resolver_signature = 1, 
            @allow_interactive_resolver = N'false', 
            @fast_multicol_updateproc = N'true',
            @check_permissions = 0, 
            @subscriber_upload_options = 0, 
            @delete_tracking = N'true', 
            @compensate_for_errors = N'false', 
            @stream_blob_columns = N'false',
            @partition_options = 0,
            @force_invalidate_snapshot = 1;

    FETCH NEXT FROM table_cursor INTO @table, @xtype
END

CLOSE table_cursor
DEALLOCATE table_cursor

I did the following tests to seek the source of error whithout finding.

When I execute the query the first time, only 'table' xtype are added, and an error occurs on schema_option for xtype 'func schema only'.

When I execute the query again, the second time, the xtype 'func schema only' are successfully added.

When I test the query for a single schema_option (either xtype 'table' or 'func schema only'), no error occurs. It's like he doesn't like to add both from the same query execution.

This is the error I get:

Msg 21222, Level 16, State 1, Procedure sp_MSaddmergeschemaarticle, Line 116 [Batch Start Line 0]
The schema options available for a procedure, function, synonym, or aggregate schema article are: 0x00000001, 0x00000020, 0x00001000, 0x00002000, 0x00400000, 0x02000000, 0x08000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

My working cheap solution is to execute the query 2 times. I search online, but not so much about, only find about schema_option value. So has anyone ever had this error before? It would be highly appreciated.

sql-server
asked on Stack Overflow Jan 8, 2021 by Landry • edited Jan 8, 2021 by marc_s

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0