1

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

Manos
  • 77
  • 1
  • 10

1 Answers1

2

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:

enter image description here

The second suggested solution is accessible via individual articles properties:

enter image description here

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.

Community
  • 1
  • 1
andrews
  • 2,173
  • 2
  • 16
  • 29
  • Thank you for your answer, I will try the first way and I will let you know. On the matter of the 3ed option, can I create the types manually? Since it is subscriber, can I alter the schema? – Manos Feb 28 '18 at 10:16
  • @Manos, if the given type is not explicitly marked as a Replication Article but is just a dependency of another table being replicated, I think you should be able to create that UDT manually on subscriber. If SQL Server won't allow you doing that, you should try the first 2 options OR as the last resort, remove subscription, create UDT and restore the subscription. But this is only the last resort and you should be careful not to lose not synchronized data. – andrews Feb 28 '18 at 10:23
  • When I include a create query before snapshot if the type does not exists everything works ok.. if the type exists and I include drop/create statement the replication fails... Any idea why? Also, I tries the 3ed way and it worked. But I cannot understand something. I scripted all types from publisher and created them on subscriber. After the successful creation (I show them all) some of them marked as missing during replication and I had to create 1 by 1.. any idea about that? – Manos Feb 28 '18 at 13:03
  • @Manos, types may be missing because replication agent tried to drop and re-create them in the past because of the `Action if name is in use` property of the individual articles. See what value you have there. Probably it is `Drop existing object and create new one.` So it tried to drop some and for other the Agent got error and couldn't finish drop command. For the pre-snapshot script, try making it `CREATE-only-if-not-exists` because some other constraints may be preventing drop to fulfill, just as Agent couldn't do that per my previous sentence. – andrews Feb 28 '18 at 13:11