February 15, 2011 at 4:51 am
I had the following error in Replication Monitor earlier:
"Cannot DROP TABLE 'dbo.MyTable' because it is being referenced by object 'MyView'. "
MyView was created WITH SCHEMABINDING, which is why the error happened.
My question is, could any other table-altering action disallowed by SCHEMABIDING be appearing as "DROP TABLE" in the error message?
My assumption was that someone had deleted MyTable at the Publisher db, and then replication tried to apply this change to the Subscriber db but it failed due to MyView (which is not present in the Publisher db).
However, the users of the application swear blind that no tables were deleted. Looking in the details for the transactions relating to that table returned by sp_browsereplcmds, I can only see creation of constraints and one primary key. Unfortunately I can't look in the default trace of the Publisher, as the server was restarted this morning after some standard pre-planned Windows patching.
I altered the view in question to not use SCHEMABINDING, and replication is now working fine, but how do I troubleshoot so that this doesn't happen in the future?
Thanks for any tips
November 29, 2011 at 10:19 am
May be you ultimately figured out the solution to your post.
When objects such as views and functions are created with schema_binding, you'll get a replication error message stating a table can't be drop because some other object depends on it. If you go to the list of articles in the publication, you can select the table in question, click on article properties and select Properties for All Table Articles. In the pop-up, expand Destination Object and under [Action if name is in use], click in the adjacent field to change the option from "Drop..." to "Truncate..." Save, re-initialize the publication and you are on your way to glory.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply