locked
Computed Column at publisher end not replicating at subscriber end RRS feed

  • Question

  • Hi I do have Publisher Table and Subscriber Table , both has same schema , Difference is publisher table has one computed column which return varchar Like Sample Below:

    CREATE FUNCTION [cimfn_FormPartition](@pKeyVal BIGINT) RETURNS VARCHAR(100) WITH SCHEMABINDING AS BEGIN [LOGIC] RETURN @Output END GO **Publisher End:** create table table1 ( [pKey] [bigint] IDENTITY (1, 1) NOT NULL , [cKey] AS dbo.cimfn_FormPartition(pKey) PERSISTED, [id] [varchar] (20) NULL , [refId] [varchar] (20) NULL , constraint [pKey] PRIMARY KEY NONCLUSTERED ( pKey ASC) ) ALTER TABLE [table1] ADD CONSTRAINT [CI_Items] UNIQUE CLUSTERED ([cKey]) ON [PRIMARY] **Subscriber End:** create table table1 ( [pKey] [bigint] NOT NULL , [cKey] [varchar] (100) NULL, [id] [varchar] (20) NULL , [refId] [varchar] (20) NULL , constraint [pKey] PRIMARY KEY NONCLUSTERED ( pKey ASC) ) ALTER TABLE [table1] ADD CONSTRAINT [CI_Items] UNIQUE CLUSTERED ([cKey]) ON [PRIMARY]

    In the Article Properties of publisher table All things I have set to false , and set ACTION IF NAME IS IN USE : Keep existing object unchanged
    When I insert data into publisher table then in subscriber tabel I am getting below output :
        **pKey cKey id	 refId
           1   NULL  Item1	i1**
    I Have addedd Article Proper and Even though subscription table is  getting null value in cKey ,and all other column has correct value.
    I have check replication monitor as well , No error is showing there.
    I more drilled down with the store procedure **(sp_browsereplcmds)** and found out the insert command does not have computed column value with this. So now again question is why Distributor is not picking computed column from publisher?

    Wednesday, July 22, 2020 2:36 PM

All replies

  • Hi Ujjwal_Jain,

    Please refer to the following threads which might help:

    SQL Server : Transactional Replication Computed Column

    Peer To Peer Replication and Persisted Computed Columns

    Best Regards,

    Amelia


    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.

    Thursday, July 23, 2020 9:46 AM
  • Thank you Amelia for your response, None of the Article you mentioned is giving answer of my question and I yes I am using Transaction Replication.

    As I have publisher Table one persisted computed column and setting publisher table article properties all option set to false , one I have set Action if name is in use : Keep existing object unchanged . and Initialize option set to No initialize.

    At subscriber end I manually created schema and table.

    So my straight question Can we publish PERSISTED computed column to subscriber end?

    If yes then How?

    If no then what is the reason?


    • Edited by Ujjwal_Jain Thursday, July 23, 2020 11:50 AM
    Thursday, July 23, 2020 11:49 AM