Add column with user-defined data types in the replicated table

Category: sql server replication

Question

User_654321 on Mon, 06 Jun 2016 07:25:00


Hello,

I'm using transactional replication in SQL Server 2012. Is it possible to replicate user-defined data types by adding a new column in the replicated table?

For example:

There are custom data types Datatype_1 (varchar (10)).

After adding a column with:

alter table Table_name
add test_coll Datatype_1 null
go

As a result, we have:

Publisher DB

column_name           Type
test_coll                    Datatype_1

Subscriber DB

column_name           Type
test_coll                    varchar

Option "Convert data types" in article properties is set to "false".

Replies

Lin Leng on Mon, 06 Jun 2016 11:44:35


Hi User_654321,

As far as I know, you cannot add a user defined datatype to subscriber by simply add a new column to publication once publication is created. After publication is created, you need to manually recreate user defined datatype on each subscriber in order to use it. Also, changes made to user defined datatype will not be replicated to subscriber.

For more information, please review MSDN Article.

If you have any other questions, please let me know.

Regards,
Lin

Hilary Cotter on Mon, 06 Jun 2016 13:20:29


Newly added columns on the publisher with a user defined data type are converted to the base data type.

If is only when the snapshot is generated that the user defined data types are not converted to the base data types if the convert data type setting is set to false.

Note that the user defined data type must already exist on the subsriber for the snapshot to be applied.

User_654321 on Tue, 07 Jun 2016 07:57:20


Thanks for answers.