none
Replication having issue when creating publication - Update or insert of view or function 'dbo.sysarticlecolumns' failed because it contains a derived or constant field.

    Question

  • Update or insert of view or function 'dbo.sysarticlecolumns' failed because it contains a derived or constant field.
    Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the WHERE clause of a SELECT statement to produce a valid query.
    Changed database context to 'xxxx'.
    Msg 156, Level 15, State 1: Incorrect syntax near the keyword 'from'. (Microsoft SQL Server, Error: 4406)
    Tuesday, June 12, 2018 5:50 AM

All replies

  • Can we see what the problem article looks like? We need to see the schema for this as well as the sp_addarticle statement.
    Tuesday, June 12, 2018 1:19 PM
    Moderator
  • Hi 

    Here is that the sp_addarticle look like on distribution db

    USE [distribution]
    GO
    /****** Object:  StoredProcedure [sys].[sp_addarticle]    Script Date: 6/13/2018 10:43:28 AM ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER procedure [sys].[sp_addarticle]
    (
        @publication					sysname,
        @article						sysname,
        @source_table					nvarchar (386) = NULL,
        @destination_table				sysname = NULL,
        @vertical_partition				nchar(5) = N'false',
        @type							sysname = NULL,
        @filter							nvarchar (386) = NULL,
        @sync_object					nvarchar (386) = NULL,
        @ins_cmd						nvarchar (255) = NULL,
        @del_cmd						nvarchar (255) = NULL,
        @upd_cmd						nvarchar (255) = NULL,
        @creation_script				nvarchar (255) = NULL,
        @description					nvarchar (255) = NULL,
        @pre_creation_cmd				nvarchar(10) = N'drop',
        @filter_clause					ntext    = NULL,
        @schema_option					varbinary(8) = NULL,
        @destination_owner				sysname = NULL,
        @status							tinyint = NULL,
        @source_owner					sysname = NULL,
        @sync_object_owner				sysname = NULL,
        @filter_owner					sysname = NULL,
        @source_object					sysname = NULL,
        @artid							int = NULL OUTPUT,
        @auto_identity_range			nvarchar(5) = NULL,
        @pub_identity_range				bigint = NULL,
        @identity_range					bigint = NULL,
        @threshold						int = NULL,
        @force_invalidate_snapshot		bit = 0,
        @use_default_datatypes			bit = 1,
        @identityrangemanagementoption	nvarchar(10) = NULL,   -- NONE, MANUAL, AUTO
        @publisher						sysname = NULL,
        @fire_triggers_on_snapshot      nvarchar(5) = N'false'
    )
    AS
    BEGIN
        set nocount on
        DECLARE @cmd nvarchar(4000)
                    ,@retcode int
                    ,@publisher_type sysname
    
        EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher        = @publisher,
                                                                            @rpcheader        = @cmd OUTPUT,
                                                                            @publisher_type    = @publisher_type OUTPUT
        
        IF @retcode <> 0
            RETURN (@retcode)
    
        select @publisher = UPPER(@publisher) 
                ,@cmd = @cmd + N'sys.sp_MSrepl_addarticle'
        
        EXEC @retcode = @cmd
                        @publication,
                        @article,
                        @source_table,
                        @destination_table,
                        @vertical_partition,
                        @type,
                        @filter,
                        @sync_object,
                        @ins_cmd,
                        @del_cmd,
                        @upd_cmd,
                        @creation_script,
                        @description,
                        @pre_creation_cmd,
                        @filter_clause,
                        @schema_option,
                        @destination_owner,
                        @status,
                        @source_owner,
                        @sync_object_owner,
                        @filter_owner,
                        @source_object,
                        @artid OUTPUT,
                        @identityrangemanagementoption,
                        @auto_identity_range,
                        @pub_identity_range,
                        @identity_range,
                        @threshold,
                        @force_invalidate_snapshot,
                        @use_default_datatypes,
                        @publisher,
                        @publisher_type,
                        @fire_triggers_on_snapshot
    
        RETURN (@retcode)
    END 

    Wednesday, June 13, 2018 5:24 AM
  • Hi Viivek,

    According to the error, it is related to the view or function dbo.sysarticlecolumns. However in your shared query, there's no dbo.sysarticlecolumns. Is this view or function in sys.sp_MSrepl_getpublisherinfo? If it is possible please share us the entire sys.sp_MSrepl_getpublisherinfo query.

    And is the shared query complete? For the first EXEC, the value is from sys.sp_MSrepl_getpublisherinfo. However the second EXEC seems odd.

    Thanks,
    Xi Jin.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 13, 2018 6:52 AM
  • Hi

    that all created on distribution db inside System Databases when using replication, it created a distribution db and inside this all this functions are system generated and i am not able to see view definition of system views, is there any way to check distribution db is corrected for there is any issue on my database used for publication , i have created a dummy database for replication and it is working fine with subscription.

    Wednesday, June 13, 2018 10:05 AM
  • What I was hoping you would share with us was this:

    exec sp_addarticle @publication = N'Publication, @article = N'Article', @source_owner = N'dbo', @source_object = N'Article', @type = N'indexed view logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x0000000000030073, @identityrangemanagementoption = N'none', @destination_table = N'Feed', @destination_owner = N'Reference', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_ReferenceFeed]', @del_cmd = N'CALL [sp_MSdel_ReferenceFeed]', @upd_cmd = N'SCALL [sp_MSupd_ReferenceFeed]'

    Then the schema for the article/table/view/storedprocedure/function you are trying to replicate which is causing this error.

    Wednesday, June 13, 2018 10:16 AM
    Moderator