Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2017-03-07   |   Comments   |   Related Tips: More > 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


Last Updated: 2017-03-07


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools