Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

SQL Replication - Post Tracer Token error due to no active subscriptions

No active subscriptions were found. The publication must have active subscriptions in order to post a tracer token.

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Jul 12, 2022·

1 min read

I was trying to get all the SQL Server replication's publication that has subscriptions. Then for each publication, post a tracer token thru cursor.

I encountered the below error.

Error:

Msg 21488, Level 16, State 1, Procedure sp_MSrepl_posttracertoken, Line 136 [Batch Start Line 4] No active subscriptions were found. The publication must have active subscriptions in order to post a tracer token.

1287_20220712_105143.png

To fix this error, I have added a WHERE clause and filtered out the virtual destination db and subscription status = 'active'

SELECT DISTINCT p.NAME, s.DEST_DB, S.STATUS
FROM syspublications AS p
INNER JOIN sysarticles AS a ON p.pubid = a.pubid
INNER JOIN syssubscriptions AS s ON s.artid = a.artid
WHERE s.DEST_DB <> 'virtual'
AND S.STATUS = 2 -- active

image.png

Impressum