Can not drop table due to replication error, though table is not being published I am attempting to drop a table in a database that is a publisher. The table in question is not being published. The error I get is:

Msg 15021, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 14 [Batch Start Line 1]
Invalid value given for parameter @procmapid. Specify a valid parameter value.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.


I've checked sysarticles to see if there's an old or orphaned record, but the table doesn't appear. Any ideas as to what the issue is here?

Edit: SQL Server 2016 RTM
TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions and a longer explanation.

Replication uses database triggers for certain things

When a database is published for transactional replication in SQL Server, as part of the set-up process several database-level triggers are created. Normally, they are:

tr_MStran_alterschemaonly
tr_MStran_altertable
tr_MStran_altertrigger
tr_MStran_alterview


You can find them by expanding your database in object explorer, then looking in "Programmability" and then "Database Triggers." They do pretty much what they say. If you were to look at the code in tr_MStran_altertable you'd see that each time this trigger fires for an ALTER TABLE statement, SQL Server uses the EventData() function to capture what's being changed, then calls another replication stored procedure that will actually capture the activity and send the related DDL transaction to the distribution database. This is different than the log reader agent that looks for and captures data changes.

These triggers are created each time you publish a database for replication, and are removed as part of removing replication as well.

New DROP TABLE support in SQL Server 2014 SP2 and SQL Server 2016 SP1

In SQL Server 2014 SP2 and SQL Server 2016 SP1, Microsoft introduced a change for SQL Server replication: Support for DROP TABLE (https://support.microsoft.com/en-us/help/3170123). Before, you couldn't drop a table that was marked for replication. This new feature, which is not enabled by default, means that the DROP TABLE statement will not throw an error anymore. To facilitate this change, a new database level trigger was added: tr_MStran_droptable. This is the trigger that fires for each drop table action, and it uses the event data to determine which table was being dropped, and if the option to support the transaction even though the table is marked for replication is enabled.

But what about "older" versions of SQL Server?

And that's where the problem comes in: this trigger only gets created by replication on versions that support it... and only removed by replication on versions that support it. So even if you decide to nuke replication with something like sp_removedbreplication this trigger won't be dropped, and continue to fire each time you try and drop a table, even if the table you're trying to drop isn't marked for replication. This can happen if:


You have a database published for replication on a version supporting this functionality (either 2016 SP1 or 2014 SP2)
You backup and restore this database to a version of SQL Server that does NOT support this functionality (like 2016 RTM or 2014 SP1)
You publish the restored database for replication.


You can test this yourself: if you disable replication and re-enable it, you'll notice the create times on the database triggers will coincide with when you re-enabled publishing, but the tr_MStran_droptable trigger remains.

Fixing the problem

To solve this problem, you can either:


Upgrade the affected instance to a patch level that supports this trigger (2014 SP2 or 2016 SP1).
Manually delete the trigger, since it isn't supported.