Skip to Content
avatar image
Former Member

TEXT column not replicating in RS15.7.1/SP120

Hi

After upgrade from RS15.1 to RS1571/SP120, one of  text column of a table is not replicating where prior to upgrade it was fine.

Here are some bullet points to understand the environment.

- Primary Server is on RS15.1 version, Replicated Server upgraded to 1571.

- Repdef is having a searchable column name  "origin_server"

- Subscription is using a where clause for "origin_server" column

- Primary/replicate table is having all three triggers.

- Searchable columns is not the part of primary key

- Data is being inserted using a VIEW defined with text column viz. xml_data but "origin_server" column name is not defined in the view.

- Insert trigger is filling up the "origin_server" value using @@servername

Issue:

----------

1. When inserting the data using view => Text column is NOT replicating to target table and comes as NULL value, but rest all the columns data are populating perfectly.

2. When inserting data directly to the table using same set of values as in View=> Again getting same result as above.

3. When inserting the data directly to the table including column "origin_server" to the insert SQL ==> Text data is replicating properly as replication was working prior to upgrade.

Action Performed:

----------------------

Outbound queue of replicated database reflects the text data values but tracing output is not showing any text data update command (like writetext / readtext ) in first two cases.

I had observed the DSIE rs_counters for replicate RS () like 57013(ExecsWritetext) , 57011(UpdatesRead) etc but counters value are also moving & suggest RS processing text column.

Could you please help me to understand this RS15.7 behavior?

-Hardeep

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 14, 2015 at 01:38 AM

    Is the text column marked 'always_replicate' or replicate_if_changed?? 

    What is the repdef setting (if any exists)???     Is autocorrection or dsi_command_convert on for this table??

    Is the trigger on the table or is an instead of trigger on the view???

    I will assume it is a trigger on the table and NOT an instead of trigger on the view.   So what we would have is:

    Begin tran

    insert into table/view (origin_server=null, texcol=some long string)

    exec trigger

    update row set origin_server=@@servername

    end exec

    commit tran

    When you insert into the view or base table - without the origin_server - the text column is inserted into the table and replicated as far as RS - but then when it hits the DIST without the origin_server field set, it tosses the row away.

    Now then when it hits the row for the update, then it sends the after image of the row - not sure why it goes as insert - that is why the question wrt autocorrection or dsi_command_convert.....

    What I think you want is an instead of trigger on the view - you may also want to set a column default of @@servername for origin_server (or better yet, change it to a materialized computed column)

    If autocorrection is on or dsi_command_convert is set to u2di, then you need to mark text columns as 'always_replicate'.   Without this, you would get missing text data because any update (such as the triggered origin_server modification would delete the existing row and reinsert the new row....but the after row image would not have any text in it if the marking was replicate_if_changed.    Consequently, it would look like the row/text was there - but then the delete would remove it and you would have a null text field.   That *could* explain why the counters are moving but the text is missing.

    You may also want to open a message and add your company to list for CR's 684820/ 684821 - which allows repdefs to include system variables (such as rs_origin_server) and allow subscriptions on them.

    Add comment
    10|10000 characters needed characters exceeded