none
Identifying if a Stored Procedure that has FOR Replication is being used RRS feed

  • Question

  • Fellow SQLers,

    I am getting ready to move a pub and sub replication to other servers.

    I happened to find a sp on the pub db that has FOR REPLICATION in the header.

    But when I dump the pub script, I can see only one filter and the filter has similar code that the sp has. The sp is NOT referenced in the Filter. Is likely this is an old/dead sp.

    How would I go about validating if this sp is actually being used for replication? I know of no other way than dumping the pub.

    Thanks

    MG

    Wednesday, November 13, 2019 11:46 PM

Answers

  • The "FOR REPLICATION" does not mean it is actually used for replication.  That flag means it is intended to only be run on the publisher and never on subscribers.  


    • Marked as answer by mg101 Thursday, November 14, 2019 2:27 PM
    Thursday, November 14, 2019 2:19 PM

All replies

  • Hi mg101,

     

    Would you please show us the sp you are confused about?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 14, 2019 1:47 AM
  • HI Dedmon,

    We think it is a dead sp.

    There is only one filter on the publisher and it does a filter on the same table with the same where.

    create procedure [dbo].[FLTR_changeitem_1__170] for replication as if exists (select * from [stageschema].[changeitem] where [FIELD] <> 'description') return 1 else return 0

    Let me know.

    Thanks,

    MG

    Thursday, November 14, 2019 1:56 PM
  • The "FOR REPLICATION" does not mean it is actually used for replication.  That flag means it is intended to only be run on the publisher and never on subscribers.  


    • Marked as answer by mg101 Thursday, November 14, 2019 2:27 PM
    Thursday, November 14, 2019 2:19 PM
  • Thanks Tom

    MG

    Thursday, November 14, 2019 2:28 PM