Computed Column at publisher end not replicating at subscriber end

Question

question

ujjwa_jain asked Jul 19, '20

Computed Column at publisher end not replicating at subscriber end

	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?

replication sql server transactional-replication sql-server-2017

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-07-19T18:44:26Z

Magnus Ahlkvist answered Jul 19, '20

I don't think the computed column can ever be replicated. However, you can create it as a computed column on the subscriber end instead.

So run the below code on your subscriber. Just remember that when reinitializing the subscription, your table will be dropped and recreated so you'll have to run this again.

ALTER TABLE table1 DROP CONSTRAINT CI_Items;
ALTER TABLE table1 DROP COLUMN cKey;
ALTER TABLE table1 ADD cKey as dbo.cimfn_FormPartition(pKey) PERSISTED;
ALTER TABLE table1 ADD CONSTRAINT CI_Items UNIQUE CLUSTERED ([cKey]) ON [PRIMARY];

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-07-20T04:54:12Z

ujjwa_jain answered Jul 20, '20

@Magnus Ahlkvist My requirement is, I donot want to do any computed column calculation at subscriber table and database , I just want to get the computed coulmn value from publisher like other columns.

Is there any specific reason why we can not replicate the value of computed column from publisher even it is PERSISTED although?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2020-07-21T04:39:55Z

ujjwa_jain answered Jul 21, '20

@Magnus Ahlkvist , @anybody can you please give me clear idea with the reason?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2020-07-23T02:57:28Z

mssqlserverdba answered Jul 23, '20

A persisted computed column will be transactionally replicated. I just tested it in my lab. Try removing the article from the publisher and subscriber and adding it back. You must have something set up wrong there.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2020-07-23T04:17:26Z

ujjwa_jain answered Jul 23, '20

mssqlserverdba I appericate your response, Can you please tell me what specific setting you did.
In my enviroment I manually create schema at subscriber end and Publisher article properties setting everthing to false , one setting set to -> Action if name is in use : Keep existing object unchanged and it is not intilized by the snapshot.
Can you please tell any specific I need to set?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question