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.
User contributions licensed under CC BY-SA 3.0