By: Tim Smith | Updated: 2017-03-07 | Comments | Related: > Replication
Problem
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?
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: SELECT * 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
a.name 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 = a.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
- See some of the past tips on replication for additional queries that will assist.
- Perform regular audits of replication's metadata and always verify post setting it up. This is the easiest time to detect an issue.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-03-07
