adding user-defined data type to existing publication/subscription

Hello, I am not a sql server programmer.
I have a database which already has a publication set up, which sends a
bunch of tables to subscribers. Everything was okay, but now I need to
publish two more tables. These tables contain columns which are user
defined types, which means my subscribers do not have them defined on
their side.
When I try to start synchronizing I get an error: Column or parameter
#1: Cannot find data type u_Blah. (error number 2715)
which is I guess, fair enough since the tables is trying to call
something that doesn't exist for the subscribers yet. If I understand
MSDN correctly (I tried reading so many articles...) in the Publication
--> Properties --> Articles tab; then, when I go into the properties
of the new articles, I can specify "Convert User Defined To base data
types", and then the subscribers will be able to pick up the user
defined types just fine. However (PLEASE tell me I understood this
wrong) that only works when publication is being set up the very first
time. That, in my situation, because I'm only adding articles, I cannot
do that.
The solution in MSDN seems to be to "manually copy the type (and the
associated assembly for a user-defined type) to each subscriber".
(msdn2.microsoft.com/en-us/library/ms152559.aspx) I have no idea what
this means. Can anyone shed some light on what must be done now?
Thank you
-Dorothy
This is definable at the article level, and if you select to use the base
types for your new articles, there should be no requirement to have them
exist on the subscriber. If you want to continue to use user-defined data
types, you can create a script to add them (using sp_addtype and operating
in the correct database ie a 'USE' statement at the top) and then run this
script on the subscriber. How to do this? There are several options:
(a) manually open up Query Analyser and connect to the subscriber and then
run there
(b) use a pre-snapshot script on the publication - not for you as you are
just adding articles to an initialized publication
(c) use sp_addscriptexec, which will run the script on the subscriber the
next time it synchronizes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Just to clarify the current situation:
1) SQL2005 snapshot processing can handle column dependencies on
user-defined data types (both TSQL and CLR) by including the necessary
type\assembly definitions as part of the snapshot. There should be no need
to manually create the necessary types at the subscriber ahead of time using
a pre-snapshot script anymore as folks typically need to do on SQL2000.
2) SQL2005 DDL replication currently lacks capability to figure out new data
type dependencies introduced by a DDL action (such as adding a new column
depending on a UDT that doesn't exist at the subscriber as you attempted
below) As such, you would need to implement some other mechanisms
(sp_addexecscript being one) to bring the necessary data type (+assembly)
definition over to the subscriber before the DDL action is replicated.
-Raymond
<dorothyy@.eyi.us> wrote in message
news:1159311634.662309.166020@.d34g2000cwd.googlegr oups.com...
> Hello, I am not a sql server programmer.
> I have a database which already has a publication set up, which sends a
> bunch of tables to subscribers. Everything was okay, but now I need to
> publish two more tables. These tables contain columns which are user
> defined types, which means my subscribers do not have them defined on
> their side.
> When I try to start synchronizing I get an error: Column or parameter
> #1: Cannot find data type u_Blah. (error number 2715)
> which is I guess, fair enough since the tables is trying to call
> something that doesn't exist for the subscribers yet. If I understand
> MSDN correctly (I tried reading so many articles...) in the Publication
> --> Properties --> Articles tab; then, when I go into the properties
> of the new articles, I can specify "Convert User Defined To base data
> types", and then the subscribers will be able to pick up the user
> defined types just fine. However (PLEASE tell me I understood this
> wrong) that only works when publication is being set up the very first
> time. That, in my situation, because I'm only adding articles, I cannot
> do that.
> The solution in MSDN seems to be to "manually copy the type (and the
> associated assembly for a user-defined type) to each subscriber".
> (msdn2.microsoft.com/en-us/library/ms152559.aspx) I have no idea what
> this means. Can anyone shed some light on what must be done now?
> Thank you
> -Dorothy
>
|||Hello,
thread has been moved to here mysteriously.
http://groups.google.com/group/micro...65417554cc611c
thank you
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Just to clarify the current situation:
> 1) SQL2005 snapshot processing can handle column dependencies on
> user-defined data types (both TSQL and CLR) by including the necessary
> type\assembly definitions as part of the snapshot. There should be no need
> to manually create the necessary types at the subscriber ahead of time using
> a pre-snapshot script anymore as folks typically need to do on SQL2000.
> 2) SQL2005 DDL replication currently lacks capability to figure out new data
> type dependencies introduced by a DDL action (such as adding a new column
> depending on a UDT that doesn't exist at the subscriber as you attempted
> below) As such, you would need to implement some other mechanisms
> (sp_addexecscript being one) to bring the necessary data type (+assembly)
> definition over to the subscriber before the DDL action is replicated.
> -Raymond
> <dorothyy@.eyi.us> wrote in message
> news:1159311634.662309.166020@.d34g2000cwd.googlegr oups.com...