I recently had a colleague request help with a replication problem he was having with a client. The client was running transactional replication from a SQL Server 2005 Publisher to multiple SQL Server 2005 Subscribers and was having issues after attempting to add several articles (tables) to an existing publication.
--
The initial error reported on multiple subscribers looked like this in Replication Monitor:
“Unable to replicate a view or function because the referenced
objects or columns are not present on the Subscriber.”
To me this said that some of the article changes that had been made during the publication change hadn’t
been properly sent to the subscribers, so I decided to reinitialize all
subscriptions:
(Another colleague pointed out that I could have reinitialized only the broken subscriptions one-by-one, but in this case a majority of the subscriptions were broken and the publication was relatively small.)
--
Three of the four subscriptions failed re-initialization with two different
errors, one with a object reference error and the other two with a replication error.
--
The first subscription, on SubServer1, failed re-initialization
with the following error:
Cannot DROP TABLE 'dbo.Table99' because it is being
referenced by object 'View1'.
I found that the referenced view was created with SCHEMABINDING,
which was preventing the related table from being dropped and recreated. The view
didn’t have any explicit permissions on it, so I scripted the view, dropped it,
re-ran initialization for that subscription, which succeeded, and then recreated
the view (again with SCHEMABINDING as it was originally).
--
SubServer2 and SubServer3 had a different but actually related
issue:
In both of these cases, the re-initialization was failing
because a table on the subscriber couldn’t drop a table being used in replication.
What this essentially means is:
In Publication #1, PubServer1 is publishing to SubServer2
In Publication #2, SubServer2 is publishing one or more of the
tables from Publication #1 to some other server or servers.
In essence, SubServer2 is both a subscriber to Publication #1 and a publisher
of Publication #2 (often called a “re-publisher”). This results in the error seen above,
because part of the default options for a re-initialization of a subscription
is to drop and recreate the tables, and when a table is an article in a
publication (in this case Publication #2 above) it cannot be dropped to
reinitialize the subscription to Publication #1.
For information on re-publishing and the order of steps required to use it, see the TechNet article here and the blog post here. There are many limitations on both the publications and the subscriptions involved in such an arrangement.
The fix to this is alter the properties of the offending
articles, or all articles, in the publication (in this case the change was made to all articles, but it could have been made one-by-one as well:
Changing article properties in this fashion automatically
marks all subscriptions for re-initialization (assuming you don’t cancel out of
the above dialog box). Your options are to accept the default as shown, in
which case you need to manually run the snapshot agent job on the Distributor
before the re-initialization will occur, or to check the “Generate the new
snapshot now” box which will reach out to the Distributor and immediately
fire the snapshot agent job, beginning the re-initialization process
right away.
The decision on what to do here should be based on how
critical the replication is to your infrastructure (if you need it fixed *now*
you may have no alternative but to generate it right away) and how large the
publication itself is (a large publication can be time- and resource-intensive
to generate and transfer a snapshot during business hours).
In this case based on the tables included in the publication it
was possible to “Generate the new snapshot now” because of its relatively small
size. This re-initialization completed successfully on all subscribers
with the “Delete Data” option configured.
--
The final item here is that my notes above about the
SCHEMABINDING view would not be completely relevant had I first discovered the
republishing situation. The SCHEMABINDING view broke that single
subscriber because the SCHEMABINDING prevented the table from being dropped,
but had the article's property been set to “Delete Data” rather than “Drop and Recreate”
the SCHEMABINDING issue would have been handled without dropping and recreating
the view.
--
Hope this helps!
