Merge Replication fails to replicate UD type

Question

I have a DB that includes some User Defined Types. As I understand, Merge Replication will automatically include the UD types that are been used. One of those types is a table variable witch is used in a function. And on publisher site works fine but on the Subscriber site it fails to synchronize. Why is this happening?

• I tried to replicate just the one function which uses the type, and succeed. After that the replication worked just fine.

• When I tried to make the same configuration to another system it failed at the begin as the original try on the previous system, but now even if I try to replicate the one procedure, I still get the same Error.

The error is the following:

The schema script 'some name.sch' could not propagated to the subscriber.

And when I trace the error:

Cannot find data type dbo.someName

Answer 1

I think the answer from Hilary Cotter in this forum should help you:

Transactional Replication and User Defined Data Types

I will quote it here in case the link gets broken:

You have the option of replicating user defined data types via a pre-snapshot script.

You also have the option of replicating user defined data types as the base data type. This is the convert data types section of the article properties dialog. By default it is false, you would want to set it to true to have them converted to a base datatype. For example if you have a user data type of varchar(11) (ie EXEC sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL') , it would be replicated as varchar(11) and not ssn.

The first option with pre-snapshot script is accessible through Properties of Publication -> Snapshot. I assume you need to provide a script there which would check if the given User Defined Type not yet exists in the Subscriber and then create it, otherwise skip:

The second suggested solution is accessible via individual articles properties:

See, if the above help you to solve your issue. Of course there is also a solution to manually create the missing UDT at subscribers.