If you tried to drop a transactional or snapshot publication, the error occurs when the specific row cannot be deleted from MSpublications.
If you tried to drop a merge publication, the error occurs in any of the following circumstances:
The relevant rows cannot be updated in or deleted from one or more of the following tables, perhaps because of insufficient permissions or a missing table: MSmerge_genhistory, MSmerge_replinfo, MSpublications, sysmergearticles, sysmergepublications, sysmergeschemaarticles, sysmergeschemachange, sysmergesubscriptions, or sysmergesubsetfilters.
A related dynamic snapshot job cannot be dropped.
Any error occurred in removing the publication's articles or their generation history.
If the publication was the last one in the database, the process tried to truncate the tables MSmerge_genhistory, MSmerge_contents, and MSmerge_tombstone, and one or more of the truncate statements returned an error.
If the error is returned along with 20040, "Could not drop the article(s) from the publication '%s'," one or more articles could not be dropped through the stored procedure sp_dropmergearticle.
If the error is returned along with 20010, "The Snapshot Agent corresponding to the publication '%s' could not be dropped," there was a problem with dropping the Snapshot Agent with the stored procedure sp_MSdropmergepub_snapshot.
If the error is returned along with 14071, "Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor," there was an error calling sp_helpdistributor for the specified publication.
If the error is returned along with 21254, "The Active Directory operation on publication '%s' could not be completed because Active Directory client package is not installed properly on the machine where SQL Server is running," or 21369, "Could not remove publication '%s' from Active Directory," it failed calling sp_ActiveDirectory_Obj.
Follow these steps:
Verify that the publication exists.
If the error was returned in combination with another error, troubleshoot the other error first.
If you are trying to drop the publication through a direct call to a stored procedure, verify that all your parameters are correct. Try different parameters or try to drop the publication through Enterprise Manager instead.
If you have previously tried to manually remove replication by directly dropping replication system tables or procedures, temporarily add those tables or procedures back to avoid "Invalid object name" errors.
In some cases, you may need to manually remove replication piece by piece.
For detailed instructions on "How to manually remove a replication in SQL Server 2000 or in SQL Server 2005", see the Microsoft Knowledge Base article, 324401.
| Target | Microsoft.SQLServer.2000.DBEngine | ||
| Category | EventCollection | ||
| Enabled | True | ||
| Event Source | $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$ | ||
| Alert Generate | True | ||
| Alert Severity | Warning | ||
| Alert Priority | Normal | ||
| Remotable | True | ||
| Alert Message |
| ||
| Event Log | Application | ||
| Comment | Mom2005ID='{8D2B3825-C039-4E6E-BF53-F40C0919431B}' |
| ID | Module Type | TypeId | RunAs |
|---|---|---|---|
| EventDS | DataSource | Microsoft.Windows.EventProvider | Default |
| GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
Home<Rule ID="Microsoft.SQLServer.2000.Could_not_drop_the_publication" Target="SQL2000Core!Microsoft.SQLServer.2000.DBEngine" Enabled="true" Comment="Mom2005ID='{8D2B3825-C039-4E6E-BF53-F40C0919431B}'" Remotable="true">
<Category>EventCollection</Category>
<DataSources>
<DataSource ID="EventDS" TypeID="Windows!Microsoft.Windows.EventProvider">
<ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
<LogName>Application</LogName>
<Expression>
<And>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery>PublisherName</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDisplayNumber</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005RegularExpression</Operator>
<Pattern>^(17052|17055)$</Pattern>
</RegExExpression>
</Expression>
<Expression>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDescription</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005BooleanRegularExpression</Operator>
<Pattern>(^.*14006 and (not ^.*\n.*14006)) or (^.*14006.*\n.*14006)</Pattern>
</RegExExpression>
</Expression>
</And>
</Expression>
</DataSource>
</DataSources>
<WriteActions>
<WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">
<Priority>1</Priority>
<Severity>1</Severity>
<AlertOwner>$Data/PublisherName$</AlertOwner>
<AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2000.Could_not_drop_the_publication.AlertMessage"]$</AlertMessageId>
<AlertParameters>
<AlertParameter1>$Data/EventDescription$</AlertParameter1>
</AlertParameters>
<Suppression>
<SuppressionValue/>
</Suppression>
</WriteAction>
</WriteActions>
</Rule>