Solving Table Drop Error Related to SQL Server Replication's sp_MStran_ddlrepl

By:   |   Updated: 2017-03-07   |   Comments   |   Related: More > Replication


We were recently trying to drop a SQL Server table in a subscriber database that did not receive replicated data, but threw an error as if the table did. The error, " Msg 15021, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 14 Invalid value given for parameter @procmapid. Specify a valid parameter value." implied that the table could not be dropped because it was part of a publication. When we checked, we verified that it was not a part of any publication. Our solution involved disabling replication, removing the objects, and starting it all over again. What caused this and is there a better solution?


When experiencing this issue, if you've verified that the table is preventing a drop, replication occurs to the database, and the publication does not specify the destination database, we can look at some of the metadata and see what it's pointing to. Let's begin by looking at sys.articles in the subscriber:

---- On the subscriber database:
FROM dbo.sysarticles

According to Microsoft, this shows us the tables involved on the subscriber side; in general, I prefer to get replication information from the distribution database's metadata, but with this error, we do need to see what the subscriber shows because the subscriber and distribution database may not be in sync (in a recent case of this, this was the issue). Look at the columns dest_table and dest_owner which are the subscriber schema and table. Do you see the table that's refusing to be dropped? When I see this error, this is why.

If you want to see what specifically prevents this drop, you can actually look at the code underneath the procedure sp_MStran_ddlrepl; provided that the table is not in the above dbo.sysarticles, you'll be able to drop the table. If it is, it will throw this error. We can test this by creating a contrived table and dropping it (notice that it won't be in dbo.sysarticles).

CREATE TABLE tblContrivedTable (ContrivedColumn VARCHAR(10))
INSERT INTO tblContrivedTable VALUES ('drop')
DROP TABLE tblContrivedTable

One solution to allow you to drop the table is to disable the trigger tr_MStran_droptable. However, you want to verify that any action taken is reflected in the dbo.sysarticles. For an example, in a few of my cases, the distribution database metadata did not match the subscriber metadata; this is a problem because there's a miscommunication and this needed to be solved. If the distribution database shows a destination owner of "admin" while the subscriber shows "dbo", I would prefer to have these synced correctly. In my case, what I showed was that replication was started with the wrong metadata, corrected ad-hoc, but those corrections were never transmitted to the subscriber metadata. You can see this in the column MessageText in the below query from the tip Queries To Debug SQL Server Replication Issues:

SELECT PublicationName
    , a.publication Publication
    , ditosu.comments AS MessageText
    , ditosu.[time] CommandDate
    , ditosu.xact_seqno xact_seqno
FROM MSdistribution_agents a
    INNER JOIN MSpublications p ON a.publisher_db = p.publisher_db
        AND a.publication = p.publication
    INNER JOIN MSdistribution_history ditosu ON ditosu.agent_id =
-- Apply a filter here can minimize the noise
ORDER BY ditosu.[time] DESC

The two suggestions that I would make to prevent this issue is to always double check on the metadata after creating a publication and verify that it matches the direction of the set up. I've witnessed quite a few cases where it did not and this is an issue. Remember, that everything must be communicated between servers and also must be updated between servers, if an update occurs. If you see an issue after setting it up, it is easy to solve it then. Second, I would recommend randomized audits of replication's metadata on all servers involved, such as the publisher, distributor and subscriber. This will assist in detecting problems if they arise.

Next Steps

Last Updated: 2017-03-07

get scripts

next tip button

About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Change Not For Replication Value for SQL Server Identity Columns

Steps to clean up orphaned replication settings in SQL Server

Troubleshooting transactional replication latency issues in SQL Server

Add new article to existing publication for SQL Server Transactional Replication

SQL Server Replication Overview

get free sql tips
agree to terms