I am just starting to play around with all the cool replication tools to Programmatically Monitor Replication
One of the early finds is sp_replmonitorhelpsubscription
When I run it as
sp_replmonitorhelpsubscription
I get
Msg 20587, Level 16, State 1, Procedure sp_replmonitorhelpsubscription, Line 77 [Batch Start Line 16]
Invalid '@publication_type' value for stored procedure 'sp_replmonitorhelpsubscription'.
According to MS documents it should be valid with a null default
NULL (default)
If I run it with the Parameter it works fine.
sp_replmonitorhelpsubscription @publication_type = '0'
I using it with transactional replication, on a single server (Reporting Copy) I have tried and I get the same results on SQL 2017 & 2016. I am running it against the distribution database
Not sure If I am doing something stupid, if the MS docs are wrong, or what.
Why does sp_replmonitorhelpsubscription not work without parameters?
Best Answer
Checking the code reveals that :
This means that if
@publication_type
is NULL then it will throw an error which is expected.Even on 2014, it is throwing same error.
Also, you just need to run with
sp_replmonitorhelpsubscription @publication_type = 0
-- for Transactional publication. since the data type isINT
.