question

ujjwa_jain avatar image
ujjwa_jain asked

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?
replicationsql servertransactional-replicationsql-server-2017
10 |1200 characters needed characters left characters exceeded

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

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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 characters needed characters left characters exceeded

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

ujjwa_jain avatar image
ujjwa_jain answered

@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 characters needed characters left characters exceeded

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

ujjwa_jain avatar image
ujjwa_jain answered

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

10 |1200 characters needed characters left characters exceeded

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

mssqlserverdba avatar image
mssqlserverdba answered

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 characters needed characters left characters exceeded

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

ujjwa_jain avatar image
ujjwa_jain answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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