I have a default on a column that disappears every time I reinitialize
replication. This is transactional replication to an updatable subscriber on
SQL Server 2005. Yes, replication of defaults is turned on, and *most* of my
column defaults do, in fact, replicate to the subscriber. The ones that fail
are distinctive in that they reference a user-defined function. And yes,
that UDF and all its internal dependencies are present on the subscriber.
(All the defaults that successfully replicate are either literal values or
else reference *intrinsic* functions, e.g. GETDATE.)
Any idea whether this is a bug? Or what? Any help would be *VERY*
appreciated!
Thanks!
Jason Fisher
Did you replicate the UDFs referenced by the defaults as well, would be
great if you can post the history of the snapshot agent as well. Thanks
much.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi Gurus,
> I have a default on a column that disappears every time I reinitialize
> replication. This is transactional replication to an updatable subscriber
> on SQL Server 2005. Yes, replication of defaults is turned on, and *most*
> of my column defaults do, in fact, replicate to the subscriber. The ones
> that fail are distinctive in that they reference a user-defined function.
> And yes, that UDF and all its internal dependencies are present on the
> subscriber. (All the defaults that successfully replicate are either
> literal values or else reference *intrinsic* functions, e.g. GETDATE.)
> Any idea whether this is a bug? Or what? Any help would be *VERY*
> appreciated!
> Thanks!
> Jason Fisher
>
|||Raymond,
Thanks for the reply. The UDF is not being *replicated* -- currently, we
aren't replicating any of the code objects (e.g., stored procedures,
functions, or triggers) -- but it was copied to the subscriber database
manually (ahead of time). It did exist there at the time replication was
initialized. But does it have to be explicitly *included* in the replication
articles? I wouldn't have thought so.
Jason
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Did you replicate the UDFs referenced by the defaults as well, would be
> great if you can post the history of the snapshot agent as well. Thanks
> much.
> -Raymond
> "Jason Fisher" <j.fisher@.earthlink.net> wrote in message
> news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
|||The problem is that we can't just assume that you have the UDFs referenced
by the defaults\check constraints to be at the subscriber ahead of time so
you have two options:
1) Explicitly include the UDFs referenced by the defaults in your
publication
2) Put the defaults referencing the unpublished UDFs in a post-snapshot
script, this basically means you know better than we do what you have at
your subscriber (After all, you did manually create the UDFs at the
subscriber right?)
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:OsG3BaxaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Raymond,
> Thanks for the reply. The UDF is not being *replicated* -- currently, we
> aren't replicating any of the code objects (e.g., stored procedures,
> functions, or triggers) -- but it was copied to the subscriber database
> manually (ahead of time). It did exist there at the time replication was
> initialized. But does it have to be explicitly *included* in the
> replication articles? I wouldn't have thought so.
> Jason
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...
>
|||Raymond,
> The problem is that we can't just assume that you have the UDFs referenced
> by the defaults\check constraints to be at the subscriber ahead of time so
> you have two options:
Hmm. But wouldn't it make more sense to replicate the default and then throw
an error later if the referenced UDF is not there on the subscriber, rather
than to simply fail silently during replication? I would always rather have
a noisy, trappable error later on than a silent, untrappable "error" earlier
on. Oh well ...
Regarding your recommendations:
> 1) Explicitly include the UDFs referenced by the defaults in your
> publication
I'm trying this now, but it doesn't look like a good solution in our
particular case because we have tables already in the subscriber database
(not being replicated from the publisher) that also reference the same UDF.
This means that what I'm seeing is error after error in the replication
monitor as it tries and fails to drop the UDF.
> 2) Put the defaults referencing the unpublished UDFs in a post-snapshot
> script, this basically means you know better than we do what you have at
> your subscriber (After all, you did manually create the UDFs at the
> subscriber right?)
I will think about trying this next, although it's not ideal because a) it's
manual, b) the tables in the script have to be updated / maintained as / if
our business needs change. In other words: again, it's manual.
Any other ideas? And thanks very much for the assistance, too, by the way!
Jason
|||> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
I believe the snapshot agent did print out messages explaining that a
default\check constraint is not scripted because it references udf not in
the publication; and believe me when I say some folks will complain the
other way if the snapshot fails to apply because we "should know better" not
to script a default constraint that is referencing something that they
"explicitly" exclude from the publication. The perfect solution will be for
us to divine what the user really wants but we are not there yet and will
not likely be there in the near future.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>
|||Now that I have thought about your case some more, you can publish the UDFs
referenced by the default constraint while making sure that the pre-creation
command for the UDF articles is 'none' instead of 'drop'. This will avoid
having the snapshot delivery process from trying to drop the UDFs at your
subscriber which is most likely the main source of problem for replicating
the UDFs to the subscriber.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>
No comments:
Post a Comment